Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
Hello,
I copy and pasted the formula. See results below. It looks like it did not work as i have differences in all.
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.