Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi everyone,
I have 2 measures that calculate the cumulative sum of the opened tickets. When I used these measures with a visual, the result isn't correct and I don't know how to solve it.
First measure
Cumulative Open Tickets =
VAR _lastDate = CALCULATE(MAX(DimDate[Date]),'Work Items')
RETURN
CALCULATE(
[Outstanding Tickets],
FILTER(
ALL(DimDate[Date]),
DimDate[Date] <= MAX(DimDate[Date])
),
DimDate[Date] <= _lastDate
)
Here, the result is always correct even when I apply a relative date filter but when there is no modification for a specific month, the measure returns nothing (not good)
Here is the second measure
Cumul Open Tickets =
VAR _lastDate = MAX(DimDate[Date])
RETURN
CALCULATE(
[Outstanding Tickets],
FILTER(
ALLSELECTED(DimDate[Date]),
DimDate[Date] <= _lastDate
)
)
Here, the measure returns always a result, even when there is no modification (very good, that's what I want) but when I apply a filter with a relative date, the result changes because the measure restarts the calculation from the first virtual month
As you see, the number of opened tickets is different according to the number of month. And that's not good because it doesn't reflect the reality. And this problem doesn't exist with the first measure.
What can I do to avoid the blank when there is no new event on the tickets and keep the real number of opened ticket (and not the number on the selected period)
Thanks
Solved! Go to Solution.
I think the problem is the 'Work Items' filter being passed to MAX. Try
Cumulative Open Tickets =
VAR _lastDate = MAX ( DimDate[Date] )
RETURN
CALCULATE (
[Outstanding Tickets],
REMOVEFILTERS ( DimDate ),
DimDate[Date] <= _lastDate
)
Try
Cumulative Open Tickets =
VAR _lastDate =
CALCULATE ( MAX ( DimDate[Date] ), 'Work Items' )
RETURN
CALCULATE (
[Outstanding Tickets],
REMOVEFILTERS ( DimDate ),
DimDate[Date] <= _lastDate
)
I think the problem is the 'Work Items' filter being passed to MAX. Try
Cumulative Open Tickets =
VAR _lastDate = MAX ( DimDate[Date] )
RETURN
CALCULATE (
[Outstanding Tickets],
REMOVEFILTERS ( DimDate ),
DimDate[Date] <= _lastDate
)
Very good @johnt75
Your DAX formula return the cumulative sum even if I apply a relative date 👍👏
But that blow my mind .....
Why
MAX ( DimDate[Date] )
and
CALCULATE ( MAX ( DimDate[Date] ), 'Work Items' )
has an impact on the final result?
If you can help me to understand
When using the Work Items table as a filter, only dates which appear in that table will be added to the filter context. So any dates which don't appear in Work Items will effectively be discarded from the Date table before running the MAX calculation. Combining this with having the months on the x-axis means that for a month where no dates exist in Work Items MAX(Date[Date]) will return blank, and so the rest of the calculation will also return blank.
Ok, very clear. Thanks for the explanations
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |