Forum Discussion
Complex Dates compare
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.
11 Replies
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?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 tablesDate 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.
The logic is that all snapshot from the LY und LLY in the same (selected) month should be shown?
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.
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 _filter4) Filter the matrix visual with the FilterData Measure (greater than or equal to 1)I hope this was a good walkthrough.
