cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tljthree Frequent Visitor
Frequent Visitor

DAX Calculation - Total for Previous Work Day - regardless of date in context

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.

 

 


Previous_WorkDay_DAX_problem.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
tljthree Frequent Visitor
Frequent Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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!

View solution in original post

7 REPLIES 7
Super User
Super User

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
tljthree Frequent Visitor
Frequent Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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.

tljthree Frequent Visitor
Frequent Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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!

View solution in original post

Microsoft v-ljerr-msft
Microsoft

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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?Smiley Happy

 

Regards

daominhtri Frequent Visitor
Frequent Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

HI @tljthree,

Could you pls show me how you create your column PreviousWorkDayDimID?

daominhtri Frequent Visitor
Frequent Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

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.

https://freeshadow-my.sharepoint.com/:u:/g/personal/daominhtri_abcda_tech/EZdjquQyGDlLuhV6ztDUgywBVq...

Waldchiller Regular Visitor
Regular Visitor

Re: DAX Calculation - Total for Previous Work Day - regardless of date in context

Hey this still works hepled me a ton 🙂

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)