I am amending the post of yesterday evening, to include a data set based on fictitious numbers.
I have a cumulative totals problem that I have been trying to resolve for the entire day, without success.
I want to show in a table (twelve months - months coming from the Date table), the
- actuals for the current year
- actuals for the previous year
- actuals for two years ago
- actuals for three years ago
- cumulative actuals for the current year
- cumulative actuals in the prior year
- cumulative actuals for two years ago
- cumulative actuals for three years ago
The cumulative totals must show the total from the start of the year (July) to the current row context.
I have written the cumulative formulas using ALLSELECTED( Dates[Date] )
You can see that the only formula that works is the cumulative total for the current year.
Any cumulative total for the previous years does not work.
Why do the previous years not work? It does not make any sense.
I marked the Dates table as a “date” table.
I would be very grateful for your help.
There is no mismatch in granularities; I linked the Fact table to the Date table; I am showing 12 months.
Had there been a mismatch, no cumulative total would have worked. As such, the current year does work; the previous years do not.