QTD doesnt work when Month slicer finds no transaction records

[nanma94]

I have the following data model: 

Data Model

In my visual, I have a year/month slicer from the DateTable to drive the display the activity count (in the Event table) by MTD, QTD, and YTD. 

Here are my MTD, QTD, adn YTD formulas: 

MTD Activity# = calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESMTD(Event[ActivityDate]))

QTD Activity# = calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESQTD(Event[ActivityDate]))

YTD Activity# = calculate(COUNTA(Event[Id]),ALLEXCEPT(DateTable,DateTable[Date]),DATESYTD(Event[ActivityDate]))

With the year/month selected in the slicer, if there are event records found for the selected month, it has no problem to show MTD, QTD and YTD. 

When there is no event records found - no event happened for the entire month for a customer - for the year/month selected in the slicer,  its expected that the MTD is blank. But I still expect to see QTD and YTD. The issue is, they are all blank. I believe the reason is, the QTD and YTD functions take ActivityDate but with no records found, there is no ActivityDate passed to the QTD, and YTD function. 

What would be a good way to address this?

Thanks a lot, 

NM

[BhaveshPatel]

Hi NM, 

It seems a basic flaw in your formula. When you are using time intelligence functions in the data model, you should either have Mark as Date Table settings active( Not available in PowerBI), Alternatively, You can use ALL(DateTable[Date]) to remove the filters on the other columns of the Date Table. 

You should try this:

[YTD Activity] := CALCULATE ( COUNTA(Event[ID]),ALL(DateTable[Date]),
                                                                     DATESYTD( 'Date'[Date] ) )

Similarly, Use it for MTD & QTD.

[nanma94 · Accepted solution]

Thanks so much Bhavesh for always there to help!

I fixe it with: 

Earlier:

QTD Activity# = calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESQTD(Event[ActivityDate]))

YTD Activity# = calculate(COUNTA(Event[Id]),ALLEXCEPT(DateTable,DateTable[Date]),DATESYTD(Event[ActivityDate]))

Now:

QTD Activity# = if (isblank(Event[MTD Activity#]), calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESQTD(DateTable[EOM])), calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESQTD(Event[ActivityDate])))

YTD Activity# = if (isblank(Event[MTD Activity#]), calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESYTD(DateTable[EOM])), calculate(COUNTA(Event[Id]), ALLEXCEPT(DateTable,DateTable[Date]), DATESYTD(Event[ActivityDate])))

I want to keep the year/month selected from slicer to apply to sales data filtering, and event data filtering. With the new formula, when no event happend in a month -> activityDate is blank using the year/month slicer filter, I will pass the end of month date of the year/month selected in the slicer to calculate QTD, and YTD. It worked out:)

Thanks again, 

NM