Forum Discussion

Griff1982's avatar
Griff1982
Copper Contributor
May 14, 2024

2 columns of dates. Pivot occassions per month

I have 2 columns of dates of tasks performed.

I need to create a chart preferably pivot to show occurances per month of each. Would I need a 3rd column as it currently just used the first for the X-Axis. Or should I use a countif to create a new table counting how many times the month/year appears in each column? Can I use countif for month/year?

 

Thanks

Charting
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 14, 2024

    Griff1982 

    If you are on Windows you may do that with data model. Be sure grouping is enabled in options

    Creating PivotTable add data to data model. 

    Next depends on your business logic. You may use dates grouped in table. Better to create in Power Pivot calendar table, create relationships

    measures

    #A:=CALCULATE( COUNTROWS(Table1), USERELATIONSHIP(Table1[DateA], 'Calendar'[Date] ))
#B:=CALCULATE( COUNTROWS(Table1), USERELATIONSHIP(Table1[DateB], 'Calendar'[Date] ))

    and create PivotTable/Chart from data model

     

     

Resources