Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a data set with two dates - Report Month and Actual Month. I am trying to create a pivot that counts the number of items that corresponds with each month as per below example. If I just put Count (Distinct Item) it duplicates counts. The closest I've come is: If(Reported Month = Actual Month, Count (Distinct Item),0) but that obviously only works partially.
Any help would be much appreciated
Data: | ||
Item | Reported Month | Actual Month |
A | January | January |
B | February | January |
C | February | February |
D | March | January |
E | March | March |
F | March | March |
Expected Pivot Table Result: | ||||
Actual Month | ||||
January | February | March | Total | |
Reported Month | ||||
January | 1 | 1 | ||
February | 1 | 1 | 2 | |
March | 1 | 2 | 3 | |
Total | 3 | 1 | 2 | 6 |
Try
Count(distinct If([Reported Month] = [Actual Month], Item))