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 community,
I'm sure this problem has been raised here but impossible to find the answer.
I have created a measure to calculate the number of active tickets (no problem) but when I use this measure in a bar chart, and when there isn't an open ticket for a certain period of time, I don't see that on my visual.
In my situation, how to visualize all the last 18 months even if there is no data between June 2021 and January 2022?
Thanks
Solved! Go to Solution.
It looks like the measure in the 2nd chart is a running total so you should be able to modify that to roll the sum from previous months into a month that is missing data. A running total measure looks like this:
Order Count Running Total =
VAR _MaxDate = MAX ( Dates[Date] )
RETURN
CALCULATE ( COUNTROWS ( Sales ), FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= _MaxDate ) )
When you set the variable _LastDate, that part of the measure is filtered by the SalesTable which means any date in the Dates[Date] that is later than the hightest date on the SalesTable is filtered out, so it returns blanks in those months where there are no sales
It looks like the measure in the 2nd chart is a running total so you should be able to modify that to roll the sum from previous months into a month that is missing data. A running total measure looks like this:
Order Count Running Total =
VAR _MaxDate = MAX ( Dates[Date] )
RETURN
CALCULATE ( COUNTROWS ( Sales ), FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= _MaxDate ) )
That's perfect @jdbuchanan71 😀👍👏
With your cumulative formula, it's perfect
Can you explain what is the difference between your formula and the one I use?
Order Count Running Total =
VAR _LastDate = CALCULATE( MAX ( Dates[Date] ) , SalesTable )
RETURN
CALCULATE (
COUNTROWS ( SalesTable),
FILTER (
ALL ( Dates[Date] ),
Dates[Date] <= MAX ( Dates[Date] )
) ,
Dates[Date] <= _MaxDate )
)
This is the measure I have used and it returns blank month
Thanks @jdbuchanan71 for your answer, it solves a part of my problem.
As you can see, for this visual, it's exactly what I want 😀
But, for another visual, I don't want to have blank bar but I want to repeat the previous bar (so the previous value of the measure). Is it possible to do that ?
Thanks
On the date axis turn on 'Show items with no data'
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |