Calculating previous workday sales

DAX DAX Calculations
1

Hi all. I am struggling with calculating previous workday sales. I have 2 sales tables (from different ERPs) which I summarize into one sales total measure by branch location. No problem calculating and filtering by month. However, I need to show a previous days sales amount column. Below is my summary sales calculation. The problem is I have 2 different dates (1 in each table) so I when I use one of the dates in the previous day calculation the other group shows total for the month and visa versa.

I just need the summary sales for the prior work day. In my date table I have a “Isworkingday” column but it is not working. Thanks!

Summary Sales =
var dwsales = SUMX(fact_DW_DailySalesSummary,fact_DW_DailySalesSummary[Sales])
var s2ksales = SUMX(‘fact_Sales Details’,‘fact_Sales Details’[Qty Ship]*‘fact_Sales Details’[Price])
return
s2ksales+dwsales

2

Hi @grjohnsonjr,

A sample PBIX file would be really helpful here.
That way we can see how the model is set up and have a full view of the attributes.

3

MartinTemplate.pbix (481.2 KB)

4

Hi @grjohnsonjr,

Only see one fact table in the model but give this a go. First calculate the previous working day.

Previous workingday = 
CALCULATE( MAX( 'New Date Table'[Date] ),
    FILTER( ALL( 'New Date Table' ),
        'New Date Table'[IsWorkingDay] = TRUE() &&
        'New Date Table'[Date] < MAX( 'New Date Table'[Date] )
    )
)

.

Then use that to calculate Sales for each like below.

Previous workingday Value = 
CALCULATE( [Total Sales],
    FILTER( ALL( 'New Date Table' ),
        'New Date Table'[Date] = [Previous workingday]
    )
)

I hope this is helpful

5

Sorry Melissa I sent you the wrong pbix. The one I’m working in is too large to upload. Below is the formula you suggested with the proper naming. The DateMaster field is related to the date fields in the 2 sales tables. Using this formula, I am getting 9/29/20 as the prior day. Should be getting 9/14/20.

Prior Day = CALCULATE(MAX(dim_Calendar[DateMaster]),
FILTER(ALL(dim_Calendar),
dim_Calendar[IsWorkingDay]=1 &&
dim_Calendar[DateMaster]<MAX(dim_Calendar[DateMaster])))

6

This sounds like a context issue because it seems to “see” the end of the month in the current filter context. What elements from the Date table are you using in the visual?

7

Ok I loaded your date table and used this formula and I’m getting current date 9/15/20

Prior Day = CALCULATE(MAX(dim_Dates[Date]),
FILTER(ALL(dim_Dates),
dim_Dates[IsWorkingDay]=TRUE() &&
dim_Dates[IsAfterToday]=FALSE() &&
dim_Dates[Date]<MAX(dim_Dates[Date])))

8

How are you visualizing that?

I dragged the date table date into a table and the measure is working fine…
image

9

Melissa - I’m sorry for the confusion!! I need the previous day, not previous day LM. So today is 9/15/20. I need the report to show the previous business day (i.e. 9/14/20). Thank you so much!

10

Oh I see your date format is different. Sorry

11

Hi @grjohnsonjr, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

12

Melissa - I finally got the following DAX to work

Prior Day Sales =
var PriorDay = [Prior Day]
return
CALCULATE([Total Sales],FILTER(ALLSELECTED(dim_Dates),dim_Dates[Date]=PriorDay))