Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating previous days volume measure after slicing date filter not working

Hello, 

 

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: 

 

Date                Impressions

1/20/2021       xxxxx

1/21/2021       xxxxx

1/22/2021       xxxxx

1/23/2021       xxxxx

 

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:

 

 

Start Date = min('table'[Date])
Last Date = Max('table'[Date])
Duration = DATEDIFF([Start Date],[Last Date],DAY)+1
Prev Begin Date = [Start Date]-[Duration]
Prev Last Date = [Start Date]-1

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!!!!!

 

Thanks

Shubham

1 ACCEPTED SOLUTION

@Anonymous , You have to use a date table marked as date table.

 

Refer to my video, why time intelligence fails - https://www.youtube.com/watch?v=OBf0rjpp5Hw

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , 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]))

 

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Anonymous
Not applicable

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

@Anonymous , You have to use a date table marked as date table.

 

Refer to my video, why time intelligence fails - https://www.youtube.com/watch?v=OBf0rjpp5Hw

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.