Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.