Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
On screen I have date slicer
Sorry for the cross post (I have posted similar question in 'Desktop' was not sure if thats a right place so posting here again)
I have measures like max date, previous month end, previous quarter end, previous year end
MaxSelectDate = MAX(Dates[Date])
PrevME = DATE(YEAR([MaxSelectDate]), MONTH([MaxSelectDate]),1)-1
PrevQE = EOMONTH(Date(Year([MaxSelectDate]),INT(DIVIDE(MONTH([MaxSelectDate])-1,3))*3,1),0)
I have certain attributes like Product A, B, C and I want to show sales for each of the product for MaxSelectDate, PrevME, PrevQE, etc.
So user selects a date in slicer. Based on selected date MaxSelectDate, PrevME, PrevQE are calculated and I show information as follows:
Sales Volume
Product | SelectDate | PrevME | PrevQE | PrevYE |
A | 100 | 98 | 99 | 101 |
B | 80 | 95 | 75 | 90 |
C | 95 | 100 | 105 | 95 |
Any tips would be appreciated
Solved! Go to Solution.
Hi @alsm in short it is not possible to create physical table based on slicer selection, as you want results of selection to be output as quarter and month (last one from selection).
One possible solution is to use my proposal and in Filter pane chosse months as you wish. There are also other alternatives but it leads to similar approach as filter: you need to create input dates for calculation for your request.
I aslo created in enclosed v2 file, two similar version of "same" measure:
Proud to be a Super User!
Hi @alsm please see enclosed file. I would say there is output the same as you wrote.
Proud to be a Super User!
Hi @some_bih
Thank you for your reply and apologies for the delay (I was out of town)
Unfortunately, I am not sure if this solution works for me.
I only want the filter to show data for dates previous month end, previous quarter end, previous year end
e.g. If max date in slicer is 25-Sep-2023. Then I want in matrix the filter to apply that data is shown only for
- 31-Aug-2023 (i.e. previous month end)
- 30-Jun-2023 (i.e. previous quarter end)
- 31-Dec-2022 (i.e. previous year end)
in the attached pbix, I also do not get why we have 2 calculated tables
1. DateAutoTemplate (which has DAX for generating date series)
2. Date which simply refers to above DateAutoTemplate
and then Date is key constraint to 'comm 24102023' table. How does this help?
Regards
Anirudh
Hi @alsm in short it is not possible to create physical table based on slicer selection, as you want results of selection to be output as quarter and month (last one from selection).
One possible solution is to use my proposal and in Filter pane chosse months as you wish. There are also other alternatives but it leads to similar approach as filter: you need to create input dates for calculation for your request.
I aslo created in enclosed v2 file, two similar version of "same" measure:
Proud to be a Super User!
Hi @some_bih
Thank you for your help.
Just to confirm my understanding of your logic. you have done two things
1. add a yes/no flag for all quarter ends
2. this dax function filters a. date range to show and then within the date range where quarter end flag = yes
is my understanding of the logic correct?
one thing i do not know (or have seen). Date table being a wrapper of DateAutoTemplate. What is the benefit of this? Is this a best practice?
Hi @alsm yes for 1 and 2.
DateAutoTemplate is "part" of solution for Date table created with DAX
Proud to be a Super User!
Hi @alsm
please share example file of data as input for expected output.
Proud to be a Super User!
Sure. Here is some mock data
RefDate | Currency | RiskMetric | Value |
30/09/2023 | TOT | A | 21,296.80 |
30/09/2023 | TOT | B | -138,659.93 |
30/09/2023 | TOT | C | -164,003.10 |
30/09/2023 | TOT | D | 730,404.88 |
31/08/2023 | TOT | A | 32,803.76 |
31/08/2023 | TOT | B | -141,519.09 |
31/08/2023 | TOT | C | -142,663.59 |
31/08/2023 | TOT | D | 754,708.40 |
31/07/2023 | TOT | A | 16,702.63 |
31/07/2023 | TOT | B | -144,969.25 |
31/07/2023 | TOT | C | -167,037.85 |
31/07/2023 | TOT | D | 794,709.95 |
30/06/2023 | TOT | A | 15,818.83 |
30/06/2023 | TOT | B | -153,137.51 |
30/06/2023 | TOT | C | -173,619.13 |
30/06/2023 | TOT | D | 866,729.01 |
31/05/2023 | TOT | A | 20,514.07 |
31/05/2023 | TOT | B | -165,267.50 |
31/05/2023 | TOT | C | -172,232.75 |
31/05/2023 | TOT | D | 585,549.83 |
30/04/2023 | TOT | A | 29,473.76 |
30/04/2023 | TOT | B | -167,138.73 |
30/04/2023 | TOT | C | -159,522.43 |
30/04/2023 | TOT | D | 640,393.78 |
31/03/2023 | TOT | A | 16,671.61 |
31/03/2023 | TOT | B | -175,674.38 |
31/03/2023 | TOT | C | -185,346.69 |
31/03/2023 | TOT | D | 757,409.66 |
28/02/2023 | TOT | A | 15,757.65 |
28/02/2023 | TOT | B | -189,935.27 |
28/02/2023 | TOT | C | -183,274.61 |
28/02/2023 | TOT | D | 671,738.76 |
31/01/2023 | TOT | A | -6,227.82 |
31/01/2023 | TOT | B | -240,631.94 |
31/01/2023 | TOT | C | -177,648.94 |
31/01/2023 | TOT | D | 656,775.42 |
31/12/2022 | TOT | A | -56,446.22 |
31/12/2022 | TOT | B | -251,760.52 |
31/12/2022 | TOT | C | -277,987.01 |
31/12/2022 | TOT | D | 771,070.28 |
Expected table I want to create
Date | A | B | C | D |
30/09/2023 | 21,296.80 | -138,659.93 | -164,003.10 | 730,404.88 |
31/08/2023 | 32,803.76 | -141,519.09 | -142,663.59 | 754,708.40 |
30/06/2023 | 15,818.83 | -153,137.51 | -173,619.13 | 866,729.01 |
31/12/2022 | -56,446.22 | -251,760.52 | -277,987.01 | 771,070.28 |