Forum Discussion
Creating a Measure based on Previous day
You should use Today() instead of Now(), because Now()-1 return current time-1 second, not previous day. And for the sample you provided, you can test it by replace Today() with Date(2020,5,19) to check the average of 5/18.
Measure Calculate(divide(sum([Time ]),sum([Sales]),0),filter('Table',[Date]= TODAY()-1))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nosaj03 , To avoid divide by 0 use divide . which you have done correctly
Calculate(divide(sum(Time),sum(Sales),0),filter(Table,[date] = Today()-1))
This might not work if date is selected then you have to use all
Calculate(divide(sum(Time),sum(Sales),0),filter(all(Table),[date] = Today()-1))
Better to use a date table
Last Day Non Continous = CALCULATE(divide(sum(Time),sum(Sales),0),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(divide(sum(Time),sum(Sales),0),dateadd('Date'[Date],-1,Day))
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
amitchandak thank you for the insight. I have a date table that I created.
When I use the formula you provided below
Day behind Sales = CALCULATE(divide(sum(Time),sum(Sales),0),dateadd('Date'[Date],-1,Day))
It is still giving me the total based on the entire table and not the previous day.
nosaj03 , can you share the screenshot of the issue. This should have worked. Hope you are using date and any other date-related column from date table
amitchandak using the exact formula you gave me the Avg Time for 5/18 should be 227
Its still giving me the AVG Time for both days of 264.
The date table I have uses Date just like in the formula but its giving me the average for all days and not the previous day
