Forum Discussion
Helper IIIVisualize all months, even those with no data
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
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 ) )
5 Replies
- jdbuchanan71
Super User
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 ) )
FredLEGUENThat'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
- jdbuchanan71
On the date axis turn on 'Show items with no data'
- FredLEGUEN
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
- jdbuchanan71
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