Forum Discussion

Nat_Pearce's avatar
Nat_Pearce
Copper Contributor
Apr 05, 2021

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

How do I have the dates show next to the names with the total number next to date and have Grand Total for all?                
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 05, 2021

Nat_Pearce 

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

Nat_Pearce's avatar
Nat_Pearce
Copper Contributor
Apr 05, 2021

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

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 05, 2021

    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? 

    • Nat_Pearce's avatar
      Nat_Pearce
      Copper Contributor
      Apr 05, 2021

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

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 05, 2021

        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.

Resources