I am trying to create measures for cumulative totals within a fiscal year but am struggling. I have one measure half working insofar as it creates a cumulative total but it’s not bounded within fiscal year. However my main challenge is the cumulative total for last year. I can’t use DATEADD or SAMEPERIODLASTYEAR because in this case “Last Year” is not 365 days ago, but, for example, “the fourth wednesday in November”. However, my date dimension conveniently provides the dateID for the relevant last-year date. But despite my efforts I cant make it return anything useful! Any help gratefully received. PBIX attached.DailySales.pbix (2.1 MB)
Hi - in this case, in the date table, I have set it to 1st October. However in the real situation it is the first Monday in October. The date table does capture Fiscal Year, if that helps. Thanks for taking a look!
@Jamie Do we have the option of removing unused Years (everything after 2021 and before 2016) from the model?
If I remove those unused years, the query runs in 600 ms otherwise it runs in 3 seconds because storage engine has to materialize 10K rows, I would suggest you remove the year you don’t need. I have created a short date table ‘Dates’ because of which Storage Engine materializes only 2.5k rows.
If we are in agreement with the current result, we can move forward with your “First Monday” requirement.
DailySales.pbix (2.3 MB)
Antriksh =
VAR MaxDate =
MAX ( Dates[TheDate] )
VAR FiscalYear =
MAX ( Dates[FiscalYear] )
VAR AllDateSales =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( factSales, Dates[TheDate], Dates[FiscalYear] ),
REMOVEFILTERS ( factSales )
),
"@Sales", [Gross Sales Actual £]
)
VAR DateList =
FILTER (
AllDateSales,
Dates[TheDate] <= MaxDate
&& Dates[FiscalYear] = FiscalYear
)
VAR Result =
SUMX ( DateList, [@Sales] )
RETURN
Result
6 Likes
That is looking amazing, nice one, now I need to compare the sales for a given DateID with the DateID that is in the field LastYearDateID, I will try and base it on the above! Thank you so much.
1 Like