Complex Dates compare

[Anonymous]

Hello Experts,

Please help me with this date comparison.

I have a dates table with Snapshots Column where we take Snapshots every other Saturday. Sometimes there may be only one snapshot per month.

Ex Dates data comes in one column. 

If the user selects CY date 12/03/2022, they want to see the same metric for LY and LLY.

CY	LY	LLY
12/31/2022	12/04/2021	12/05/2020
12/03/2022	12/04/2021	12/05/2020
11/12/2022	11/13/2021	11/14/2020
10/29/2022	10/30/2021	10/31/2020
10/15/2022	10/30/2021	10/31/2020
10/01/2022	10/02/2021	10/03/2020
09/17/2022	09/18/2021
09/03/2022	09/18/2021
08/20/2022	08/28/2021
07/30/2022	07/31/2021
07/16/2022	07/31/2021
07/02/2022	07/03/2021
06/18/2022	06/19/2021
05/28/2022	05/29/2021
05/14/2022	05/01/2021
04/30/2022	04/17/2021
04/16/2022	04/17/2021
04/02/2022	04/03/2021
03/19/2022	03/20/2021
02/26/2022	02/27/2021

Expercted Output:

CY	12/03/2022	100
LY	12/04/2021	299
LLY	12/05/2020	150

Thanks.

[andhiii079845]

My solution:
1) First you need a second table "Dimtable" with distinct date values from the other table (Via Power Query Editor: reference table and remove duplicate )

2) Create a slicer with the Dimtable Date column and matrix with date, sales column from the table "example" 

 

3) DAX Formular:

FilterData = 

VAR _selectmonth2 = MONTH(SELECTEDVALUE(Dimtable[Date]))
VAR _currentmonth = MONTH(maxx(example,example[Date]))
VAR  _filter = IF(_currentmonth=_selectmonth2,1,0)

RETURN _filter

4) Filter the matrix visual with the FilterData Measure (greater than or equal to 1)

I hope this was a good walkthrough. 

 

[Anonymous]

Could you please share the dataset with me?

Thank you

[andhiii079845]

The link:
https://wetransfer.com/downloads/73897095db391911505c5acaa37feefd20230225151617/6d1cfe9fe7a0cb091788...

[Anonymous]

Hi @andhiii079845 ,

Your solution is good for Month Selection. I want it by date.

EX:If the user Selects 

CY	12/03/2022

The output should be like below.

CY	12/03/2022	100
LY	12/04/2021	299
LLY	12/05/2020	150

Thanks a lot for your time. 

[andhiii079845]

What do you mean per date? The slicer is via date ans than looking for the same month in a other year. I not really understand the logic. 

[Anonymous]

 sorry 

If the user selects data 12/03/2022,  then the result should be these dates 

CY	12/03/2022	100
LY	12/04/2021	299
LLY	12/05/2020	150

The goal is to compare CY date with previous year dates sales amount.

[andhiii079845]

Okay, what is exactly wrong in my solution? Do you want to calculate a sum per month or something like this? 

[andhiii079845]

The logic is that all snapshot from the LY und LLY in the same (selected) month should be shown? 

[Anonymous]

@andhiii079845 

Yes, That's correct.

All these are examples.

If they select CY date the patten is like below.

CY	LY	LLY
12/31/2022	12/04/2021	12/05/2020
12/03/2022	12/04/2021	12/05/2020
11/12/2022	11/13/2021	11/14/2020
10/29/2022	10/30/2021	10/31/2020
10/15/2022	10/30/2021	10/31/2020
10/01/2022	10/02/2021	10/03/2020
09/17/2022	09/18/2021
09/03/2022	09/18/2021
08/20/2022	08/28/2021
07/30/2022	07/31/2021
07/16/2022	07/31/2021
07/02/2022	07/03/2021
06/18/2022	06/19/2021
05/28/2022	05/29/2021
05/14/2022	05/01/2021
04/30/2022	04/17/2021
04/16/2022	04/17/2021
04/02/2022	04/03/2021
03/19/2022	03/20/2021
02/26/2022	02/27/2021

Thank you.

[andhiii079845]

I have some question to it: 
1) Date is MM/DD/YYYY in your example? 
2) Are CY,LY,LLY separate columns? Where are the metrics? Do you have a example for it?
3) If you select one month and there are more snapshot in this month, which one should be selected: all? the last one? 

[Anonymous]

Hi @andhiii079845 ,

Thank you for looking into it.

I have some question to it: 
1) Date is MM/DD/YYYY in your example?  Yes, Its a date column.
2) Are CY,LY,LLY separate columns? They all come from one date column. I seperated them by year to explain better.
Where are the metrics? I added test data (Please see below)
Date table Key joind to Fact tables

Date	Sales
12/31/2022	125
12/03/2022	126
11/12/2022	127
10/29/2022	128
10/15/2022	129
10/01/2022	130
09/17/2022	131
09/03/2022	132
08/20/2022	133
07/30/2022	134
07/16/2022	135
07/02/2022	136
06/18/2022	137
05/28/2022	138
05/14/2022	139
04/30/2022	140
04/16/2022	141
04/02/2022	142
03/19/2022	143
02/26/2022	144
12/04/2021	145
12/04/2021	146
11/13/2021	147
10/30/2021	148
10/30/2021	149
10/02/2021	150
09/18/2021	151
09/18/2021	152
08/28/2021	153
07/31/2021	154
07/31/2021	155
07/03/2021	156
06/19/2021	157
05/29/2021	158
05/01/2021	159
04/17/2021	160
04/17/2021	161
04/03/2021	162
03/20/2021	163
02/27/2021	164
12/05/2020	165
12/05/2020	166
11/14/2020	167
10/31/2020	168
10/31/2020	169
10/03/2020	170

Do you have a example for it? Yes added 
3) If you select one month and there are more snapshot in this month, which one should be selected: all? the last one?  

CY	12/03/2022	126
LY	12/04/2021	145
LLY	12/05/2020	165

This is good example.

They took two snapshots in 2022 , but previous year only one. 

If users selects 12/31/2022, or 12/03/2022 , the pattern shold be like below.

12/31/2022	12/04/2021	12/05/2020
12/03/2022	12/04/2021	12/05/2020

Thanks a lot.