Filter Dax Calculation based upon YTD Sum

Question

In a Tabular SSAS Model, I'm trying to count the number of distinct customers that purchased a given product wtihin a YTD Timeframe. The table contains measures that aren't explicit sums, so I get the Cartesian Product of all products for each customer, regardless of no sales. I'm attempting to limit the count by filtering out customer / product combinations with YTD Sales = 0. However, I cannot get the FILTER to recognize the DATESYTD context. It only ever filters based upon Sales existing within the chosen calendar month. I've tried inserting the ALL function every which way.

This is what I have so far.

Measure: 
CALCULATE (
      DISTINCTCOUNT ( Fact[Customer] ), 
      DATESYTD ( Calendar[Date] ), 
      FILTER ( Fact, 
           CALCULATE ( [Sum of Sales], DATESYTD ( Calendar[Date] ) ) <> 0 
      )
 )

This measure will, for example, count distinct customers purchasing a product in Month #5 if Month #5 is explicitly chosen. It will not, however, include a customer that purchased that item in Month #2 of the same year.

Dan Dan · Accepted Answer · 2018-06-15T14:52:19

I think the following DAX should do the trick:

COUNTROWS(
    FILTER(
        VALUES(Fact[Customer]),
        CALCULATE ( [Sum of Sales], DATESYTD ( Calendar[Date] ) ) <> 0 
    )
)

Also, make sure your 'Calendar' table has been marked as a date table. If, for some reason, you prefer not to mark it as a date table, rewrite the above DAX to:

COUNTROWS(
    FILTER(
        VALUES(Fact[Customer]),
        CALCULATE ( [Sum of Sales], DATESYTD ( Calendar[Date] ), ALL('Calendar') ) <> 0 
    )
)

Edit: Do you have records in your fact table where [Sum of Sales] is 0? If not, then you could simplify and improve the performance considerably by writing:

CALCULATE(
    DISTINCTCOUNT(Fact[Customer]),
    DATESYTD( Calendar[Date] )
)

Again, if you haven't marked your 'Calendar' table as a date table, add ALL(Calendar) to remove the filter on specific calendar columns.

Filter Dax Calculation based upon YTD Sum

1 Answers