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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks vicky_. 

 

I've used that exact example (plus others) and I am missing something... I feel the article has two data sources, and also two date tables. I am not sure why two date tables are required? I can create a date table (i.e. as below). But I am not sure it needs joining (I assume not as a lot of date tables just sit there alone) and how it relates to the date field in my data?

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

 I'm missing something... Just not sure what the link is...

 

Thanks for looking.

Arch

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Actually - by referencing the [Sales] with my source table as in sum('Source'[required column]) it seems to work... Let me muck around with it a bit more, and if it works I'll accept your solution 🙂