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.
I have a bit of DAX to work out how many records need to be completed in order to meet an end of year deadline. It works out how many weeks are left in the year, and then divides the amount to be done by remaining weeks.
However, the answer seems to be wrong. In my example when i manually divide 1187 by 6 weeks, the answer should be 197.8 but my measure is giving me 208.
What have i done wrong? My dax is:
Weekly Target LB =
VAR Weeks =
DATEDIFF(TODAY(),ENDOFYEAR('Date table'[Date]),DAY)/7
VAR OS =
COUNTROWS(FILTER('Metadata',Metadata[Status] <> "RecordComplete"))
RETURN
OS / Weeks
Solved! Go to Solution.
Weekly Target LB =
VAR Weeks =
round(DATEDIFF(TODAY(),ENDOFYEAR('Date table'[Date]),DAY)/7,0)
VAR OS =
COUNTROWS(FILTER('Metadata',Metadata[Status] <> "RecordComplete"))
RETURN
OS / Weeks
or
Weekly Target LB =
VAR Weeks =
rounddown(DATEDIFF(TODAY(),ENDOFYEAR('Date table'[Date]),DAY)/7,0)
VAR OS =
COUNTROWS(FILTER('Metadata',Metadata[Status] <> "RecordComplete"))
RETURN
OS / Weeks
Please consider this solution and leave kudos
ENDOFYEAR returns the last date of the year in the current context for the specified column of dates.
Check if the last date in yout Date table is 31/12/2020 and that the date table is not filtered with slicer, row, column or filter pane context.
Try write the dax measures into smaller chunks and write each value to a card, so you can check where it went wrong.
Or post the PBIX here so we can check it. Good luck
Thanks very much!
Weekly Target LB =
VAR Weeks =
round(DATEDIFF(TODAY(),ENDOFYEAR('Date table'[Date]),DAY)/7,0)
VAR OS =
COUNTROWS(FILTER('Metadata',Metadata[Status] <> "RecordComplete"))
RETURN
OS / Weeks
or
Weekly Target LB =
VAR Weeks =
rounddown(DATEDIFF(TODAY(),ENDOFYEAR('Date table'[Date]),DAY)/7,0)
VAR OS =
COUNTROWS(FILTER('Metadata',Metadata[Status] <> "RecordComplete"))
RETURN
OS / Weeks
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |