Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Comparing the same variable for two points in time from one data set

Hi Team,   I have a set of Data. Something like the below - the source table.   What I'd like to do is compare Sales and Count for Date X and Date Y. So I would have two separate sliders, one for...
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    You need two date tables so that you can select 2 different date ranges using 2 slicers. The below can be achieved through filtering on two different date columns (while it might be possible for one date column and one date table, this isn't best practice so using 2 date tables is best)

    I'm unsure what you mean by joining? if you mean creating a relationship, then yes you will need to create an inactive relationship between the two calendar and also one (active) relationship between one of the calendar tables and your month column. 

     

    You can pretty much just copy the code from the article i linked earlier, and change the name.

    i.e for sales:

    Sales = SUM(Table[Sales]) // this will take the date range selected in the table with the active relationship

    and 

    Comparison Sales :=
CALCULATE (
    [Sales],
    ALL ( 'Calendar' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Other Calendar'[Date] )
) // this will take the date range selected in the other slicer
vicky_'s avatar
vicky_
Super User
2 years ago

You need two date tables so that you can select 2 different date ranges using 2 slicers. The below can be achieved through filtering on two different date columns (while it might be possible for one date column and one date table, this isn't best practice so using 2 date tables is best)

I'm unsure what you mean by joining? if you mean creating a relationship, then yes you will need to create an inactive relationship between the two calendar and also one (active) relationship between one of the calendar tables and your month column. 

 

You can pretty much just copy the code from the article i linked earlier, and change the name.

i.e for sales:

Sales = SUM(Table[Sales]) // this will take the date range selected in the table with the active relationship

and 

Comparison Sales :=
CALCULATE (
    [Sales],
    ALL ( 'Calendar' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Other Calendar'[Date] )
) // this will take the date range selected in the other slicer
Anonymous's avatar
Anonymous
Not applicable
2 years ago
Comparison Sales :=
CALCULATE (
    [Sales],
    ALL ( 'Calendar' ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Other Calendar'[Date] )
) // this will take the date range selected in the other slicer

I'm still persisting with the solution you have aided me with...

 

For the above - does the [Sales] column not need a reference to the source table it comes from? And why is ALL ( 'Calendar' ) used? 


I am very new to this - so thanks for your help. I am normally good at 'stealing' code and manipulating it for my needs. Just lack an understanding.