Visualize all months, even those with no data

[FredLEGUEN]

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

[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

[jdbuchanan71 · Accepted solution]

@FredLEGUEN 

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 ) )

[FredLEGUEN]

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 

[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]

On the date axis turn on 'Show items with no data'