Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Power BI gurus,
I have amounts posted each work day, and would like to be able to reliably calculate the amount from the Previous Work Day, in the context of the current Work Day. See the snip below - the red column is the one I'm trying to calculate.
Things that I have tried:
(a) The Previousday() function doesn't help, because I want to jump back to the previous work day, or the previous day that I have data.
(b) Using today() doesn't work, because I don't always want the calculation to be from today's standpoint. So logic that uses TODAY() (like the following), isn't a solution:
(weekday(today(),2)
,1,TODAY()-3,
7,TODAY()-2,
TODAY()-1)
Thanks for your help!
Tom in Indy
Solved! Go to Solution.
After further review, with stimulus from Matt, along with the guys from SQLBI, I found a solution.
I added another column to my Calendar table - PreviousWorkDayDimID, populating it with the DateDimID (YYYYMMDD integer) of the Previous work day.
Then, I made my formula the following:
PremiumAmount_PriorWorkday_total:= CALCULATE ([PremiumAmount_total],
FILTER (
ALL ('Calendar'),
'Calendar'[DateDimID]=max('Calendar'[PreviousWorkDayDimID])
)
...
Thanks for the help!
Convert your surrogate date key to a date
Create a calendar table and join to your data table
make sure your calendar table has an ID column (integer) starting at 1 for the first working day and advancing by 1 for every subsequent working day. Leave non working days blank
then the following formula will work
sales yesterday = calculate(sum(table[sales]),filter(all(calendar),calendar[id]=max(calendar[id])-1))
here are some articles for you to read and learn
http://exceleratorbi.com.au/power-pivot-calendar-tables/
http://exceleratorbi.com.au/dax-time-intelligence-beginners/
Hi @MattAllington,
I'm not sure what go wrong but when I apply below code, it return total sale of the second last working day of my Calendar table. For example: today is 16 Jan 2019, last working day is 15 Jan 2019, it rerurn sale of last working day of my calendar table is 30 Dec 2019
sales yesterday = calculate(sum(table[sales]),filter(all(calendar),calendar[id]=max(calendar[id])-1)).
Here is my file.
Hey this still works hepled me a ton 🙂
Matt,
Thank you for your answer. It gets me thinking more.
Your proposed calc gets the NEXT TO LAST day's sales, but won't do it backward throughout the calendar (I don't think). If I have the following dates, it won't show previous work day sales for each line.
Sales Date ----- Sales ----- PrevWorkdaySales
3/14/2017 100 90
3/13/2017 90 125
3/10/2017 125
I believe your proposed formula will just show PrevWorkdaySales (90) on the 3/14 line.
I was looking to find PrevWorkdaySales in context.
Thanks again for your input.
After further review, with stimulus from Matt, along with the guys from SQLBI, I found a solution.
I added another column to my Calendar table - PreviousWorkDayDimID, populating it with the DateDimID (YYYYMMDD integer) of the Previous work day.
Then, I made my formula the following:
PremiumAmount_PriorWorkday_total:= CALCULATE ([PremiumAmount_total],
FILTER (
ALL ('Calendar'),
'Calendar'[DateDimID]=max('Calendar'[PreviousWorkDayDimID])
)
...
Thanks for the help!
HI @tljthree,
Could you pls show me how you create your column PreviousWorkDayDimID?
Hi @tljthree,
Great to hear the problem got resolved! Could you accept the helpful reply as solution to help others who may have similar issue easily find the answer and close this thread?
Regards
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
85 | |
69 | |
49 |
User | Count |
---|---|
141 | |
115 | |
111 | |
59 | |
59 |