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 Date X and one for Date Y. And I would return Sales for Date X and Date Y.

 

I have tried to duplicate the table below, to join to itself on Group A, Group B, and Group C. However, as it is a many to many join, it does not work well returning Sales from the primary and then secondary (the duplicate of the primary) table.

 

I feel I need to create a date table to join to. But the examples I have seen in this forum do not seem to work. I am not sure what to join this table to. If I need some DAX formula to manipulate Sales and Count.

 

I have not that much experience with DAX, except trying to get this to work, however, am willing to learn.

 

I'd love to have a chart with a bar for Sales for Date X and a separate bar with Sales for Date Y. 

 

Thanks in advance, and it seems a simple request... Apologies I cannot find an already published solution.

 

Arch,

 

MonthGroup AGroup BGroup CSalesCount
31-Jan-23SingaporeHouseOnline2341
31-Jan-23SingaporeHouseOnline4002
31-Jan-24SingaporeHouseOnline3004
31-Jan-24New ZealandHouseIn Store2202
31-May-23New ZealandBusinessIn Store1003
31-May-23New ZealandBusinessIn Store1232
31-May-23AustraliaBusinessIn Store1234
31-May-23AustraliaBusinessOnline2345
31-May-23AustraliaBusinessIn Store2353
31-Aug-23SingaporeHouseIn Store2341
31-Aug-23SingaporeBusinessOnline4002
31-Aug-23SingaporeHouseOnline3004
31-Aug-23New ZealandHouseIn Store2202
31-Aug-23New ZealandBusinessIn Store1003
31-Aug-23New ZealandBusinessIn Store1232
30-Sep-23AustraliaBusinessIn Store1234
30-Sep-23AustraliaHouseOnline2345
30-Sep-23AustraliaBusinessOnline2353

 

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

5 Replies

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