cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shubhy04
Helper I
Helper I

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

@shubhy04 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

 

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

 

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

@shubhy04 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!