Apr 05 2021 06:16 AM
How do I have the dates show next to the names with the total number next to date and have Grand Total for all?
Apr 05 2021 06:38 AM
Do you mean to show list of all related to the name dates (unique dates)?
Apr 05 2021 07:19 AM
@Sergei Baklan Yes, I believe so. Since today is 4/5/2021, I should be able to select todays date but still have the grand total for the month of April. If that makes sense.
Apr 05 2021 07:29 AM
Sorry, still not sure I understood correctly. So, you would like to select in filter or by slicer concrete date (not the period, e.g. from Jan to Jun 2021) and receive in Grand Total total number of dates for the year/month of the selected date. Against each name it shall be the list of dates for this name for the selected year/month, correct?
Apr 05 2021 08:03 AM - edited Apr 05 2021 08:04 AM
@ Sergei Baklan - Correct! Against each name it shall be the list of dates for this name for the selected year/month and day. So in the PivotTable Fields. How do I add in the 'Dates'? At the bottom in the 'Rows' section, it currently just has the names 'UWC'.
Apr 05 2021 09:40 AM
@Nat_Pearce , thank you. That's bit different, not only yearmonth for the selected date, but result for the selected date.
Since you didn't provide sample file I played on this model
Creating the PivotTable add data to data model
When create measures
For the selected date
This Date:=VAR filterdate=ISFILTERED(Range[Date])
VAR filterName=ISFILTERED(Range[Name])
VAR selectedDate=IF(filterdate, VALUES(Range[Date]), blank())
VAR combineDate=IF(filterName, CONCATENATEX(Range, FORMAT(Range[Date], "mm/dd/yyyy"),"; "), "")
RETURN IF(filterdate && filterName, CALCULATE( CONCATENATEX(Range, FORMAT(Range[Date], "mm/dd/yyyy"),"; "), FILTER(ALL(Range[Date]), Range[Date]=selectedDate)), IF(filterName, combineDate, ""))
and
Count Selected Date:=VAR filterdate=ISFILTERED(Range[Date])
VAR selectedDate=IF(filterdate, VALUES(Range[Date]), blank())
RETURN IF(filterdate, CALCULATE( [Count], Range[Date]=selectedDate), [Count])
If for the selected yearmonth they could be
Count:=COUNTROWS(Range)
All Dates:=VAR filterdate=ISFILTERED(Range[Date])
VAR filterName=ISFILTERED(Range[Name])
VAR yearmonth=IF(filterdate, EOMONTH(VALUES(Range[Date]),0), blank())
VAR combineDate=IF(filterName, CONCATENATEX(Range, FORMAT(Range[Date], "mm/dd/yyyy"),"; "), "")
RETURN IF(filterdate && filterName, CALCULATE( CONCATENATEX(Range, FORMAT(Range[Date], "mm/dd/yyyy"),"; "), FILTER(ALL(Range[Date]), EOMONTH(Range[Date],0)=yearmonth)), IF(filterName, combineDate, ""))
Count Dates:=VAR filterdate=ISFILTERED(Range[Date])
VAR yearmonth=IF(filterdate, EOMONTH(VALUES(Range[Date]),0), blank())
RETURN IF(filterdate, CALCULATE( [Count], EOMONTH(Range[Date],0)=yearmonth), [Count])
Please check in attached file.
Apr 05 2021 11:01 AM
Apr 05 2021 11:10 AM
Apr 05 2021 12:29 PM
Apr 06 2021 05:09 AM
Apr 06 2021 05:21 AM
@Sergei Baklan Ok, I figured it out. I needed to use a different browser to attach the file. Here you go!
Apr 06 2021 06:56 AM
Got it, thank you. Based on sample variant, could you please clarify which variant of filtering do you need. And shall we repeat each date in the string, e.g. "04-01-2021; 04-01-2021; 04-01-2021; 05-02-2021" or just "04-01-2021; 05-02-2021"
Apr 06 2021 08:11 AM
Apr 06 2021 08:47 AM
If like this
shall we show only one date in This Date; or don't show them at all and use only slicer; or repeat the date as above?
Apr 06 2021 09:24 AM
Apr 06 2021 10:09 AM
You may use such DAX measure
This Date:=IF (
HASONEVALUE ( Range[UWC] ),
CONCATENATEX (
VALUES ( Range[Date] ),
FORMAT ( Range[Date], "mm/dd/yyyy" ),
"; ",
Range[Date]
),
""
)
to have result as
Please check attached.