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_TblID NameSTVALBNKMthYrMis_Date1John1100Dec-22B131/12/20221John2235Dec-22B131/12/20221John2175Dec-22B231/12/20221John140Dec-22B231/12/20222James1300Dec-22B131/12/20222James2543Dec-22B131/12/20222James2200Dec-22B231/12/20222James11500Dec-22B231/12/20223Job1600Dec-22B231/12/20223Job2300Dec-22B131/12/20223Job2110Dec-22B231/12/20225Ken17546Dec-22B131/12/20225Ken170Dec-22B231/12/20225Ken2100Dec-22B131/12/20221John2100Jan-23B131/01/20231John2100Jan-23B231/01/20231John11000Jan-23B131/01/20231John1300Jan-23B231/01/20232James250Jan-23B131/01/20232James250Jan-23B231/01/20233Job1300Jan-23B131/01/20233Job2543Jan-23B131/01/20234Mark21000Jan-23B231/01/20234Mark1543Jan-23B231/01/20235Ken17546Jan-23B131/01/20235Ken2100Jan-23B131/01/20235Ken1100Jan-23B231/01/20235Ken27546Jan-23B231/01/2023 Date_TblMthYrQuarterYearQrYrDays in MonthIndexMIS_DateMvt.42099Mvt.31031/12/2099Feb-23120232023,Q128128/02/2023Jan-23120232023,Q131231/01/2023Dec-22420222022,Q431331/12/2022Nov-22420222022,Q430430/11/2022Oct-22420222022,Q431531/10/2022Sep-22320222022,Q330630/09/2022Expected Output: Mvt.Jan-23Dec-22 B1B2TOTALB1B2TOTALB1B2TOTALIDNAMEST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotalST 1_VALST 2_VALTotal1John900-135765260252851160-11010501000100110030010040013002001500100235335401752151404105502James-300-493-793-1500-100-1600-1800-593-23930505005050010010030054384315002001700180074325433Job300243543-600433-167-300676376300543843000300543843 30030060011071060041010104Mark00054310001543543100015430005431000154354310001543 000000005Ken0003076467676307646767675461007646100754676467646764615292754610076467007076161007716 Looking for expert advise.Regards,Stanley

Helper I

3 years ago

Solved

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

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

DAX

Help

Anonymous

3 years ago

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.

Column level difference between t.pbix129 KB

Anonymous
3 years ago
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

4 Replies

Anonymous

Not applicable

3 years ago

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.

Column level difference between t.pbix129 KB

stanleycj
Helper I
3 years ago
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
  Not applicable
  3 years ago
  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

Forum Discussion

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

4 Replies

Related content

Different schema into different destination

Color based on slicer selection but with different forms, Scatter chart

How to select different dates

Select three different columns using measure

Appending Selected Columns from Different Tables