Forum Discussion
Adding a +1 inside Date function results in an error but when adding +1 outside works fine, why?
Hi TallPolishAdam ,
I think the final two measures should be correct.
In your first code, you add "- WEEKDAY([Date of Report], 2) + 1" in Date() function. Power BI will check whether year/month/day parts all correct, then return date value. So if day part return Negative numbers, Power BI will return error.
Week of Report = DATE ( YEAR ( [Date of Report] ), MONTH ( [Date of Report] ), DAY ( [Date of Report] ) - WEEKDAY ( [Date of Report], 2 ) + 1 )The final two measures, both add "- WEEKDAY([Date of Report], 2) + 1" outof date, so Power BI will do calculation on [Date of Report] and think "- WEEKDAY([Date of Report], 2) + 1" as days.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, that is correct. DATE() would likely end up being 2022/11/0 with the +1 in there which fails. But when I remove the +1 the function correctly outputs 2022/10/30 instead of the expected error that would be associated with 2022/11/-1. I feel like either both should work or neither.
Hi TallPolishAdam ,
I think the final two measures should be correct.
In your first code, you add "- WEEKDAY([Date of Report], 2) + 1" in Date() function. Power BI will check whether year/month/day parts all correct, then return date value. So if day part return Negative numbers, Power BI will return error.
Week of Report = DATE ( YEAR ( [Date of Report] ), MONTH ( [Date of Report] ), DAY ( [Date of Report] ) - WEEKDAY ( [Date of Report], 2 ) + 1 )The final two measures, both add "- WEEKDAY([Date of Report], 2) + 1" outof date, so Power BI will do calculation on [Date of Report] and think "- WEEKDAY([Date of Report], 2) + 1" as days.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.