Calculating previous days volume measure after slicing date filter not working
I'm trying to achive a % change measure for a KPI that calculates the previous days volume and compare it against the current filtered date range volume but the measure to calculate previous days volume is not working. Here is the sample dataset:
Lets say i used a slicer and filtered the table for 22nd & 23rd Jan data and now want to show what is the % change in impressions over previous 2 days. Here are the measures i created to calculate total impressions during previous days:
Prev Impressions: CALCULATE (Sum('table'[impressions]),'table'[Date] >= Prev Begin Date&& 'table'[Date] <= Prev Last Date)
I dont know why but its giving me BLANK everytime, i have checked the filter interactions as well (its in scope), i have checked the date format between 'Table'[Date] column and measures as well and its the same, and even made sure that there is no other filter acting on it but its just not working. Any help with this will be really appreciated!!!!!
@shubhy04 , With help from date table, measure like
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date]))) Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1)) Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacadsqlbiMy Video Series Appreciate your Kudos.
Hi @amitchandak , thanks for replying!! I took a look at the day intelligence functions like ParallelPeriod but it does not offer to shift the dates with Day granularity, like this:
CALCULATE(SUM('table'[impressions]),PARALLELPERIOD('table'[Date],[Duration],Day)) - Not working
The previousday function only gives me the volume for (start date-1), Plus i dont know if i can use ALL function as i have kept slider date just for here, but in reality i'm using different slicers in the dashboard in addition to date column