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.
My data has a date surrogate key, but doesn't really follow a clean calendar. For example, a period may have key of 201750 and a prior period key of 201650. So I don't think I can use time intelligence functions to calculate what the period fact is.
I could easily calculate the prior period by subtracting -100 from the current period, but I'm not sure how to pass that back to DAX.
This is what I tried but didn't work:
Measure = calculate(distinctcount(mytable[customer_id]), filter(mytable,mytable[period] = mytable[period]-100))
Could someone please help?
Solved! Go to Solution.
Try something like this...
Measure = CALCULATE ( DISTINCTCOUNT ( mytable[customer_id] ), FILTER ( ALL ( mytable ), mytable[period] = MAX ( mytable[period] ) - 100 ) )
So thanks to Mark Russo. I found the proper solution.
http://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/
So thanks to Mark Russo. I found the proper solution.
http://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/
Try something like this...
Measure = CALCULATE ( DISTINCTCOUNT ( mytable[customer_id] ), FILTER ( ALL ( mytable ), mytable[period] = MAX ( mytable[period] ) - 100 ) )
I found my error:
Measure =
CALCULATE (
DISTINCTCOUNT ( mytable[customer_uid] ),
FILTER ( ALL ( mytable[period] ), mytable[period] = MAX( mytable[period]) -100)
)
This worked. Now, why is the grand total summing incorrectly. It shows the prior period and not the sum for all prior periods
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |