Previous Day Function (Weekends excluded)

[gmasta1129]

Hello,

I have data for Monday to Friday only. 

I am using the formula below to compare the previous day minus today's values.  My issue is with Monday's calculation showing that there is a difference since it cannot find Sunday's data.  What i need the formula to do is to exclude sat and sun so that the formula finds Friday's data and subtracts it by Mondays data.  

DOD NAV Difference = SUM(daily_report[total value usd])-CALCULATE(SUM(daily_report[total value usd]),PREVIOUSDAY('daily_report'[date time]))

[Fowmy · Accepted solution]

@gmasta1129 

You can use the following measure to get the desired results.

Difference =
VAR __CurrentDate =
    MAX ( 'daily_report'[date time] )
VAR _LastDate =
    LASTDATE (
        FILTER (
            ALL ( 'daily_report'[date time] ),
            'daily_report'[date time] < __CurrentDate
                && NOT WEEKDAY ( 'daily_report'[date time], 2 ) IN { 6, 7 }
        )
    )
VAR __Result =
    CALCULATE ( SUM ( daily_report[total value usd] ), _LastDate )
RETURN
    __Result

[gmasta1129]

Hello,

I copy and pasted the formula.  See results below.  It looks like it did not work as i have differences in all.  

[Fowmy]

@gmasta1129 

It's hard to understand how you have set up your model. If you could share a dummy file then I can check it and revert to you.

 

[Fowmy · Accepted solution]

@gmasta1129 

You can use the following measure to get the desired results.

Difference =
VAR __CurrentDate =
    MAX ( 'daily_report'[date time] )
VAR _LastDate =
    LASTDATE (
        FILTER (
            ALL ( 'daily_report'[date time] ),
            'daily_report'[date time] < __CurrentDate
                && NOT WEEKDAY ( 'daily_report'[date time], 2 ) IN { 6, 7 }
        )
    )
VAR __Result =
    CALCULATE ( SUM ( daily_report[total value usd] ), _LastDate )
RETURN
    __Result

[gmasta1129 · Accepted solution]

Hello,

It worked! i just made a small change to the VAR Result portion of the formula you sent.  

Thank you so much for your help!!! I was working on it for hours to try and figure it out before i went to this website.