Comparison of data using two date slicers

DAX DAX Calculations
1

Hi everyone,

I have a normal structure of Date Dimension (DateKey) & a related Fact table - for the sake of this question, let’s say, a Sales fact table, linked to the Date Dimension table by the DateKey.
Let’s call this Date Dimension “Date A”.
When I put a slicer with date & I choose any date, obviously the Sales table gets filtered. This is already working correctly.

Now, I want to add a second date table into the picture - let’s say the structure is identical to my Date Dimension. The aim is to use this second date slicer as a Comparative Date.

For example: I choose the 8th of April in my first date slicer & the 1st of April in the second date slicer.

MEASURE 1
Sales amount =

VAR ActualDateKey = SELECTEDVALUE( ‘DateA’[DateKey] )

RETURN
CALCULATE(
SUM( SalesAmt),
‘FactSales’[DateKey] = ActualDateKey
)

MEASURE 2
Sales amount (comp) =

VAR ComparativeDateKey = SELECTEDVALUE( ‘CompDate’[DateKey] )

RETURN
CALCULATE(
SUM( SalesAmt),
‘FactSales’[DateKey] = ComparativeDateKey
)

The problem here is that Sales amount (comp) is always returning a null value.

Am I missing something here?

Thanks for your reply.

Kind regards, Yogesh

2

Hi @YogeshM,

Do you have an image of the data model you can show please?.

Thanks,

3

Hi @YogeshM,

Please see if this article helps you to slove the issue.

4

Hi Marc,

Unfortunately, owing to privacy & confidentiality issues, I cannot share the data model.

Picture it this way:
Date dimension DateA (with primary key DateKey) is linked to the main FactSales (which also has DateKey).

Date dimension CompDate (which also has DateKey) is not linked to FactSales.

I have 2 slicers: one for DateA and one for CompDate.
When I select a date from DateA, it filters my FactSales (as it should). This is my BenchmarkSales.
Now, I would like to select a date from CompDate and get the ComparativeSales.

Normally, I would do:
ComparativeSales =
VAR CompDate = SELECTEDVALUE( ‘CompDate’[DateKey])
RETURN
CALCULATE(
SUM( ‘FactSales’[SalesAmt] ),
‘FactSales’[DateKey] = CompDate
)

Logically this should output me a value, isn’t it? As of now, it doesn’t. It is returning blank.

5

Hi Melissa,

Many thanks for your solution; I will definitely give it a try and give you a feedback here.

Kind regards,
Yogesh

6

Hi Melissa,

I confirm that your solution works! I have already marked it as the solution for this topic.

Thanks everyone for your much appreciated time and assistance.

Kind regards,
Yogesh