Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
4 years ago
Solved

Previous Day Function (Weekends excluded)

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]))

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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's avatar
    gmasta1129
    4 years ago

    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.  

     

     

     

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      gmasta1129
      Resolver I
      4 years ago

      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.  

       

       

       

  • gmasta1129's avatar
    gmasta1129
    Resolver I
    4 years ago

    Hello,

     

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

     

     

    • Fowmy's avatar
      Fowmy
      Super User
      4 years ago

      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.