MoM% Returning (Blank)

[Anonymous]

I have several quick measures that are returning (Blank) Values. I'm using a date table marked date table.  The simplest is here. COMs_20200604[Number] refers to the individual case number.   

Measure:  

Total Cases = COUNT(COMs_20200604[Number])

Quick Measure:

Calculation is Month-over-Month Change

Base Value - Total Cases

Date - 'Date Table'[Date]

# periods - 1

Quick Measure DAX:

Total Cases MoM% =

VAR __PREV_MONTH = CALCULATE([Total Cases], DATEADD('Date Table'[Date], -1, MONTH))

RETURN

    DIVIDE([Total Cases] - __PREV_MONTH, __PREV_MONTH)

I'm trying to calculate change in customer support case volume MoM and then building on top of that I'd like to move to change in open cases and change in cases open >90 days.  All the above are returning blank and I can't figure out why.

[v-xicai · Accepted solution]

Hi @Anonymous ,

The DATEADD function requires contiguous date selections ,so make sure your date table have contiguous date. If not, you may create a calendar table using DAX below. Then create relationship between your fact table and the calendar table with Both of Cross filter direction .

Calendar =CALENDARAUTO()

You may put [Month] and [Total Cases MoM%] into table visual to display the result.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try like this

VAR __PREV_MONTH = CALCULATE([Total Cases], datesmtd(DATEADD('Date Table'[Date], -1, MONTH)))

Also, make sure you calendar table is marked as date table

[ryan_mayu]

@Anonymous 

Can you try add the month filter and select a month to see if this works?

I think you didn't identify the month, the card shows total value, then can't show the pervious month value.