topic Re: Showing data for set of dates in DAX Commands and Tips

Showing data for set of dates

alsm — Mon, 23 Oct 2023 13:11:20 GMT

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

Re: Showing data for set of dates

some_bih — Tue, 24 Oct 2023 09:56:54 GMT

Hi alsm

please share example file of data as input for expected output.

Re: Showing data for set of dates

alsm — Tue, 24 Oct 2023 16:13:18 GMT

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

Re: Showing data for set of dates

some_bih — Tue, 24 Oct 2023 22:01:19 GMT

Hi alsm please see enclosed file. I would say there is output the same as you wrote.

Re: Showing data for set of dates

alsm — Sun, 29 Oct 2023 10:57:03 GMT

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

Re: Showing data for set of dates

some_bih — Sun, 29 Oct 2023 17:56:37 GMT

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:

- SUM Quarters_and_MonthsBeforeQuarters - include quarter dates and months before quarter months, like May and June

- SUM EndOfQuarters, include only quarters

Re: Showing data for set of dates

alsm — Sun, 29 Oct 2023 21:07:04 GMT

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?

Re: Showing data for set of dates

some_bih — Sun, 29 Oct 2023 21:20:21 GMT

Hi alsm yes for 1 and 2.

DateAutoTemplate is "part" of solution for Date table created with DAX