Time Intelligence in Direct Query

[Sri95]

Hi, I'm trying to build a time intelligence function for SPLY. I have a fact table and it has 3 dates. 1 date has an active relationship with the calendar table. And the other 2 dates have an inactive relationship. All my DAX are in the below format. I have a disconnected table for Date Type with Date 1, Date 2, Date 3 as text. Depending on the date selection, the values in my cards and visuals change. For each of the date type, I created a bookmark cos I have slicers for Year and Date Range. For example, when I select Date 1, I have the Year_Date1 column as one slicer and Date_Date1 column in the date range slicer. Similarly, I have bookmarks for Date 2 and Date 3 as well where the columns used in the slicers change respectively as I hide and unhide slicers acc to the date selected. The below is not working in Direct query mode. Please help. 

When I select multiple years in the slicer as well, SPLY will show the max year - 1 data in the PY calculation, and that is my requirement. 
I tried 

Please help!


SPLY Total Count = 
VAR SelectedDateType = SELECTEDVALUE(DateTypeBasisTable[Date Type Basis])
VAR Total_SPLY =
   SWITCH(
       TRUE(),
       SelectedDateType = "Date 1",
           CALCULATE(
               DISTINCTCOUNT(Sales[Sales ID]),
               SAMEPERIODLASTYEAR('Calendar'[Date])
           ),
       SelectedDateType = "Date 2",
           CALCULATE(
               DISTINCTCOUNT(Sales[Sales ID]),
               USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 2]),
               SAMEPERIODLASTYEAR('Calendar'[Date])
           ),
       SelectedDateType = "Date 3",
           CALCULATE(
               DISTINCTCOUNT(Sales[Sales ID]),
               USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 3]),
               SAMEPERIODLASTYEAR('Calendar'[Date])
           ),
       BLANK()
   )
RETURN
   Total_SPLY

[suparnababu8]

Hi @Sri95 

try with this measure. I just altered your measure in the highlighted color. 

SPLY Total Count =
VAR SelectedDateType = SELECTEDVALUE(DateTypeBasisTable[Date Type Basis])
VAR MaxYear = MAX('Calendar'[Year])
VAR Total_SPLY =
SWITCH(
TRUE(),
SelectedDateType = "Date 1",
CALCULATE(
DISTINCTCOUNT(Sales[Sales ID]),
FILTER(
ALL('Calendar'),
'Calendar'[Year] = MaxYear - 1
)
),
SelectedDateType = "Date 2",
CALCULATE(
DISTINCTCOUNT(Sales[Sales ID]),
USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 2]),
FILTER(
ALL('Calendar'),
'Calendar'[Year] = MaxYear - 1
)
),
SelectedDateType = "Date 3",
CALCULATE(
DISTINCTCOUNT(Sales[Sales ID]),
USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 3]),
FILTER(
ALL('Calendar'),
'Calendar'[Year] = MaxYear - 1
)
),
BLANK()
)
RETURN
Total_SPLY

Give a try and let me know if it works.

Thanks!

[amitchandak]

@Sri95 , better to have measures first like

M3 = CALCULATE(
DISTINCTCOUNT(Sales[Sales ID]),
USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 3]))

or try use like

SelectedDateType = "Date 3",
CALCULATE(CALCULATE(
DISTINCTCOUNT(Sales[Sales ID]),
USERELATIONSHIP('Calendar'[Date], 'Sales'[Date 3]) ) ,
SAMEPERIODLASTYEAR('Calendar'[Date])
),