DAX [SSAS Tabular] DISTINCTCOUNT with lastnonempty date

Question

I am analysing product distribution data. We want a snapshot measurement of market penetration [Depth].

Depth := DIVIDE (
   [Count of ranged product/store distribution points],
   [Count of audited product/store distribution points]
)

I have 2 tables: 'Distribution' (audited distribution points); and 'Calendar' (a date table). They are related in the model.

For a snapshot at 30 June 2015, I don't want to include new product/stores from November 15, but I do want include any data points that were audited in the prior 3 months.

Logically the numerator is a filtered subset of the denominator so I need the denominator first, which is where I am stuck.

If I just do a basic distinctcount without any fancy code I get this.

Month, Denominator
Mar-15, 1
Apr-15, 0
May-15, 0
Jun-15, 2
Jul-15, 6
Aug-15, 5
Sep-15, 1
Oct-15, 40
Nov-15, 53
Dec-15, 92

But I want something that looks like this:

Month, Denominator
Mar-15, 150
Apr-15, 150
May-15, 150
Jun-15, 170 -- add 1 new product in 20 stores
Jul-15, 170
Aug-15, 170
Sep-15, 170
Oct-15, 200 -- add 1 New product in 30 stores
Nov-15, 200
Dec-15, 200

I need to drop the filter context on Distribution[Date] and apply a filter on Distribution of Distribution[Date] <= Calendar[Date] and then do the distinct count but I get errors.

Count of audited product/store distribution points:=
CALCULATE(
   COUNTROWS ( 
      VALUES ( Distribution[ProductStoreKey])
   ), 
   NOT ( Distribution[Status On Exit] = "Ineligible" ),
   FILTER (
      ALL ( Distribution[Date] ),
      Distribution[Date] <= Calendar[Date]
   )   
)

ERROR:
The value for column 'Date' in table 'Distribution' cannot be determined in 
the current context. Check that all columns referenced in the calculation  
expression exist, and that there are no circular dependencies. This can also 
occur when the formula for a measure refers directly to a column without 
performing any aggregation--such as sum, average, or count--on that column. 
The column does not have a single value; it has many values, one for each 
row of the table, and no row has been specified.

It might be a error-proofing of the filter using HASONEVAUE. I'm not sure.

Among other ideas, I've tried rewriting the filter but this doesn't work either.

  FILTER (
     Distribution,
     Distribution[Date] <= 
        CALCULATE (
           MAX(Distribution[Date]),
           Distribution[Date]<=Calendar[Date] 
        )
  )

Error:
The expression contains multiple columns, but only a single column can be 
used in a True/False expression that is used as a table filter expression.

This code gets the DistibutionDate of the last datapoint at variable Calendar[Date] but I cant figure out how to incorporate it.

Last Ever Dist Date:=    
CALCULATE ( 
   LASTDATE( Distribution[DATE] ), 
   DATESBETWEEN(
      Calendar[date],
      BLANK(),
      LASTDATE(Calendar[Date])
   ),
   ALL(Calendar)
)

GregGalloway GregGalloway · Accepted Answer · 2016-01-08T02:05:58

How about:

Count of audited product/store distribution points:=
CALCULATE(
   DISTINCTCOUNT(Distribution[ProductStoreKey]),
   DATESBETWEEN(
      Calendar[date],
      BLANK(),
      LASTDATE(Calendar[Date])
   ),
   ALL(Calendar)
)

DAX [SSAS Tabular] DISTINCTCOUNT with lastnonempty date

1 Answers