Pivot Table help to show dates next to names with Grand total (i.e Aimee 4/2/2021)

Occasional Contributor

How do I have the dates show next to the names with the total number next to date and have Grand Total for all?  

 

Nat_Pearce_0-1617627861099.png       

Nat_Pearce_1-1617627891745.png

 

 

15 Replies

@Nat_Pearce 

Do you mean to show list of all related to the name dates (unique dates)?

@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.

 

@Nat_Pearce 

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? 

@ 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'.

Nat_Pearce_0-1617635040145.png

 

@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

image.png

Creating the PivotTable add data to data model

image.png

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.

@Sergei Baklan - It's an existing pivot table. I will attach the file for you.

@Sergei Baklan How do I send you a file? It won't allow me to attach.

@Nat_Pearce 

Replying on post click on browse and select the file to upload

image.png

I'm not seeing that option on my end.

@Sergei Baklan  Ok, I figured it out.  I needed to use a different browser to attach the file.  Here you go!

@Nat_Pearce 

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"

I would like it to filter and count each date string. In the file, if I clicked on the filter icon next to Row Labels, it should show each date string in April and under it list the dates that I can select which one or select all: 4/1/2021; 4/2/2021; etc. to show next to the names.

@Nat_Pearce 

If like this

image.png

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?

@Nat_Pearce 

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

image.png

Please check attached.