Forum Discussion

FredLEGUEN's avatar
FredLEGUEN
Icon for Helper III rankHelper III
4 years ago
Solved

Visualize 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 me...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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 ) )
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

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's avatar
    FredLEGUEN
    Icon for Helper III rankHelper III
    4 years ago

    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