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

[Archenar]

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,

Month	Group A	Group B	Group C	Sales	Count
31-Jan-23	Singapore	House	Online	234	1
31-Jan-23	Singapore	House	Online	400	2
31-Jan-24	Singapore	House	Online	300	4
31-Jan-24	New Zealand	House	In Store	220	2
31-May-23	New Zealand	Business	In Store	100	3
31-May-23	New Zealand	Business	In Store	123	2
31-May-23	Australia	Business	In Store	123	4
31-May-23	Australia	Business	Online	234	5
31-May-23	Australia	Business	In Store	235	3
31-Aug-23	Singapore	House	In Store	234	1
31-Aug-23	Singapore	Business	Online	400	2
31-Aug-23	Singapore	House	Online	300	4
31-Aug-23	New Zealand	House	In Store	220	2
31-Aug-23	New Zealand	Business	In Store	100	3
31-Aug-23	New Zealand	Business	In Store	123	2
30-Sep-23	Australia	Business	In Store	123	4
30-Sep-23	Australia	House	Online	234	5
30-Sep-23	Australia	Business	Online	235	3

[Archenar]

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_ · Accepted solution]

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

[Archenar]

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 🙂

[Archenar]

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.

[vicky_]

Have a read of this article: https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

best practice is to create a disconnected date table so that you can select two separate ranges without the filters interfering with each other.