Column level difference between two selected dates (ie. Max date - Min date)

[stanleycj]

Dears,

Greetings!

I need the difference between any selected max(date) - min(date) ie. Jan23 - Dec23, as an "Mvt. details" as shown below output format.

Detail_Tbl

ID	Name	ST	VAL	BNK	MthYr	Mis_Date
1	John	1	100	Dec-22	B1	31/12/2022
1	John	2	235	Dec-22	B1	31/12/2022
1	John	2	175	Dec-22	B2	31/12/2022
1	John	1	40	Dec-22	B2	31/12/2022
2	James	1	300	Dec-22	B1	31/12/2022
2	James	2	543	Dec-22	B1	31/12/2022
2	James	2	200	Dec-22	B2	31/12/2022
2	James	1	1500	Dec-22	B2	31/12/2022
3	Job	1	600	Dec-22	B2	31/12/2022
3	Job	2	300	Dec-22	B1	31/12/2022
3	Job	2	110	Dec-22	B2	31/12/2022
5	Ken	1	7546	Dec-22	B1	31/12/2022
5	Ken	1	70	Dec-22	B2	31/12/2022
5	Ken	2	100	Dec-22	B1	31/12/2022
1	John	2	100	Jan-23	B1	31/01/2023
1	John	2	100	Jan-23	B2	31/01/2023
1	John	1	1000	Jan-23	B1	31/01/2023
1	John	1	300	Jan-23	B2	31/01/2023
2	James	2	50	Jan-23	B1	31/01/2023
2	James	2	50	Jan-23	B2	31/01/2023
3	Job	1	300	Jan-23	B1	31/01/2023
3	Job	2	543	Jan-23	B1	31/01/2023
4	Mark	2	1000	Jan-23	B2	31/01/2023
4	Mark	1	543	Jan-23	B2	31/01/2023
5	Ken	1	7546	Jan-23	B1	31/01/2023
5	Ken	2	100	Jan-23	B1	31/01/2023
5	Ken	1	100	Jan-23	B2	31/01/2023
5	Ken	2	7546	Jan-23	B2	31/01/2023

Date_Tbl

MthYr	Quarter	Year	QrYr	Days in Month	Index	MIS_Date
Mvt.	4	2099	Mvt.	31	0	31/12/2099
Feb-23	1	2023	2023,Q1	28	1	28/02/2023
Jan-23	1	2023	2023,Q1	31	2	31/01/2023
Dec-22	4	2022	2022,Q4	31	3	31/12/2022
Nov-22	4	2022	2022,Q4	30	4	30/11/2022
Oct-22	4	2022	2022,Q4	31	5	31/10/2022
Sep-22	3	2022	2022,Q3	30	6	30/09/2022

Expected Output:

		Mvt.	Jan-23	Dec-22
		B1	B2	TOTAL	B1	B2	TOTAL	B1	B2	TOTAL
ID	NAME	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total	ST 1_VAL	ST 2_VAL	Total
1	John	900	-135	765	260	25	285	1160	-110	1050	1000	100	1100	300	100	400	1300	200	1500	100	235	335	40	175	215	140	410	550
2	James	-300	-493	-793	-1500	-100	-1600	-1800	-593	-2393	0	50	50	0	50	50	0	100	100	300	543	843	1500	200	1700	1800	743	2543
3	Job	300	243	543	-600	433	-167	-300	676	376	300	543	843	0	0	0	300	543	843		300	300	600	110	710	600	410	1010
4	Mark	0	0	0	543	1000	1543	543	1000	1543	0	0	0	543	1000	1543	543	1000	1543		0	0	0	0	0	0	0	0
5	Ken	0	0	0	30	7646	7676	30	7646	7676	7546	100	7646	100	7546	7646	7646	7646	15292	7546	100	7646	70	0	70	7616	100	7716

Looking for expert advise.

Regards,

Stanley 

[Anonymous · Accepted solution]

Hi @stanleycj 

You can refer to the following example.

1.Create a table

Type = SUMMARIZE(Detail_Tbl,[BNK]) 

Then put it into a slicer

2.Create a new calculated column in date table

Month = IF([MthYr]="Mvt.",0,MONTH([MIS_Date]))

3.Then create three measure

Measure 1 = var a=FILTER(ALLSELECTED(Date_Tbl),[Month]<>0)
var b=SELECTCOLUMNS(ALL(Detail_Tbl),"a",MONTH(Detail_Tbl[MthYr]))
var c=FILTER(Detail_Tbl,[BNK] in VALUES('Type'[BNK])&&[ST]=1)
return IF(SELECTEDVALUE(Date_Tbl[MthYr])="Mvt.",CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MINX(a,[Month])))-CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MAXX(a,[Month]))),IF(OR(SELECTEDVALUE(Date_Tbl[Month])=MINX(a,[Month]),SELECTEDVALUE(Date_Tbl[Month])=MAXX(a,[Month]))&&SELECTEDVALUE(Date_Tbl[Month]) in b,CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=SELECTEDVALUE(Date_Tbl[Month])))))

Measure 2 = var a=FILTER(ALLSELECTED(Date_Tbl),[Month]<>0)
var b=SELECTCOLUMNS(ALL(Detail_Tbl),"a",MONTH(Detail_Tbl[MthYr]))
var c=FILTER(Detail_Tbl,[BNK] in VALUES('Type'[BNK])&&[ST]=2)
return IF(SELECTEDVALUE(Date_Tbl[MthYr])="Mvt.",CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MINX(a,[Month])))-CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MAXX(a,[Month]))),IF(OR(SELECTEDVALUE(Date_Tbl[Month])=MINX(a,[Month]),SELECTEDVALUE(Date_Tbl[Month])=MAXX(a,[Month]))&&SELECTEDVALUE(Date_Tbl[Month]) in b,CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=SELECTEDVALUE(Date_Tbl[Month])))))

Total = [Measure 1]+[Measure 2]

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @stanleycj 

You can refer to the following example.

1.Create a table

Type = SUMMARIZE(Detail_Tbl,[BNK]) 

Then put it into a slicer

2.Create a new calculated column in date table

Month = IF([MthYr]="Mvt.",0,MONTH([MIS_Date]))

3.Then create three measure

Measure 1 = var a=FILTER(ALLSELECTED(Date_Tbl),[Month]<>0)
var b=SELECTCOLUMNS(ALL(Detail_Tbl),"a",MONTH(Detail_Tbl[MthYr]))
var c=FILTER(Detail_Tbl,[BNK] in VALUES('Type'[BNK])&&[ST]=1)
return IF(SELECTEDVALUE(Date_Tbl[MthYr])="Mvt.",CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MINX(a,[Month])))-CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MAXX(a,[Month]))),IF(OR(SELECTEDVALUE(Date_Tbl[Month])=MINX(a,[Month]),SELECTEDVALUE(Date_Tbl[Month])=MAXX(a,[Month]))&&SELECTEDVALUE(Date_Tbl[Month]) in b,CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=SELECTEDVALUE(Date_Tbl[Month])))))

Measure 2 = var a=FILTER(ALLSELECTED(Date_Tbl),[Month]<>0)
var b=SELECTCOLUMNS(ALL(Detail_Tbl),"a",MONTH(Detail_Tbl[MthYr]))
var c=FILTER(Detail_Tbl,[BNK] in VALUES('Type'[BNK])&&[ST]=2)
return IF(SELECTEDVALUE(Date_Tbl[MthYr])="Mvt.",CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MINX(a,[Month])))-CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=MAXX(a,[Month]))),IF(OR(SELECTEDVALUE(Date_Tbl[Month])=MINX(a,[Month]),SELECTEDVALUE(Date_Tbl[Month])=MAXX(a,[Month]))&&SELECTEDVALUE(Date_Tbl[Month]) in b,CALCULATE(SUM(Detail_Tbl[VAL]),FILTER(c,MONTH([MthYr])=SELECTEDVALUE(Date_Tbl[Month])))))

Total = [Measure 1]+[Measure 2]

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stanleycj]

Hi @Anonymous ,

Greetings!

I have to show both B1 and B2 whereas I have slicer for MthYr Selection , Also I am creating the measure in the detail_tbl not in date_tbl. Got some understanding on filters from your code. Let me take your sample and let me try to fit into my developement and let you know ASAP. Thanks again for your support.

[Anonymous · Accepted solution]

Hi @stanleycj 

I placed the type column in the column, and if you want to show b1, b2 can drill down

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more

[stanleycj]

Hi @v-xinruzhu-msft ,

A small concern on the exisitng code, when we select Jan 23 and Jan 22. It doesn't work, since we are using month(), I tried to use with longdate "20230131" and index but i am facing a challenge. Could you pls suggest me.

Regards,

Stanley