So I started to work of your first file.
- And this didn’t contain a Dates table. It is important to realise that a Dates table is required and needs to be marked as a date table for any kind of time intelligence calc.
- It also didn’t contain any avenues you’d explored - which can be beneficial in pointing you towards relevant content.
There’s a lot of content within Enterprise DNA resources and the forum, please use the search option in the right upper corner to find more relevant content. Here are a few resources on the Dates table:
Next I created a buch of measures to calculate the Dates, Quantity and %Change with this result:
Most required dates had a fixed offset, so to calculate them this pattern will do:
Last Week's date =
VAR ThisDate = SELECTEDVALUE( Dates[Date] )
This is not the case however for the last month’s date:
Last Month's date =
VAR Yesterday = [Yesterday's date]
VAR LastMonth =
CALCULATE( MAX( Dates[Date] ),
DATEADD( FILTER( DATESMTD( Dates[Date] ), Dates[Date] = Yesterday ), -1, MONTH )
Once the dates are calculated the basic pattern to calculate the QTY becomes easy:
Yesterday's QTY =
Dates[Date] = [Yesterday's date]
And finally the % change:
% change in quantity daily =
IF( HASONEVALUE( Dates[Date] ) && NOT( ISBLANK( [Two days ago QTY] )),
DIVIDE( [Two days ago QTY], [Yesterday's QTY] ) -1,
I hope this is helpful. Here is your sample file.
eDNA - % Quantity change.pbix (66.4 KB)