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.
Can someone please assist me in calculating week-to-date, month-to-date, and year-to-date, assuming I have the below formula:
Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
For the past 60 days, I used this formula and then used it as a visual filter
Last60Days = (IF(AND([Date] >= [Today]-60, [Date] <= [Today]),1,0))
I would like to do the same thing for WTD, MTD, and YTD. However, I would like it to autoupdate, not just selecting the month or week.
Thanks!
Solved! Go to Solution.
Apologies, was trying to hack your formula and I think I got a paren in the wrong place, try this one:
ThisWeek = (IF(AND([Date] >= [Today]-WEEKDAY([Today]), [Date] <= [Today]),1,0))
As for DATESMTD and DATESYTD, these return a column of date values and are generally used in conjunction with a date table. Potentially, you could use the same formula as above coupled with a MIN statement, so somthing like:
ThisWeek = (IF(AND([Date] >= [Date]>MIN(DATESYTD(DateTime[DateKey]), [Date] <= [Today]),1,0))
Month to date and year to date should be relatively easy:
DATESMTD
https://msdn.microsoft.com/en-us/library/ee634359.aspx
DATESYTD
https://msdn.microsoft.com/en-us/library/ee634221.aspx
Not sure about Week to Date, perhaps use WEEKDAY
https://msdn.microsoft.com/en-us/library/ee634550.aspx
in a formula like:
ThisWeek = (IF(AND([Date] >= [Today]-WEEKDAY([Today], [Date]) <= [Today]),1,0))
First, thanks for your reply. I tried the formula, and I got this error below:
Also, I am very new to PowerBI, and not quite sure how to to the MTD and YTD calculations so that it returns a 1 or a 0. Ideally, I wanted to create all these calculations as a measure, so I can put it all in one chart like below:
Apologies, was trying to hack your formula and I think I got a paren in the wrong place, try this one:
ThisWeek = (IF(AND([Date] >= [Today]-WEEKDAY([Today]), [Date] <= [Today]),1,0))
As for DATESMTD and DATESYTD, these return a column of date values and are generally used in conjunction with a date table. Potentially, you could use the same formula as above coupled with a MIN statement, so somthing like:
ThisWeek = (IF(AND([Date] >= [Date]>MIN(DATESYTD(DateTime[DateKey]), [Date] <= [Today]),1,0))
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.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |