Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stanleycj
Helper I
Helper I

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 NameSTVALBNKMthYrMis_Date
1John1100Dec-22B131/12/2022
1John2235Dec-22B131/12/2022
1John2175Dec-22B231/12/2022
1John140Dec-22B231/12/2022
2James1300Dec-22B131/12/2022
2James2543Dec-22B131/12/2022
2James2200Dec-22B231/12/2022
2James11500Dec-22B231/12/2022
3Job1600Dec-22B231/12/2022
3Job2300Dec-22B131/12/2022
3Job2110Dec-22B231/12/2022
5Ken17546Dec-22B131/12/2022
5Ken170Dec-22B231/12/2022
5Ken2100Dec-22B131/12/2022
1John2100Jan-23B131/01/2023
1John2100Jan-23B231/01/2023
1John11000Jan-23B131/01/2023
1John1300Jan-23B231/01/2023
2James250Jan-23B131/01/2023
2James250Jan-23B231/01/2023
3Job1300Jan-23B131/01/2023
3Job2543Jan-23B131/01/2023
4Mark21000Jan-23B231/01/2023
4Mark1543Jan-23B231/01/2023
5Ken17546Jan-23B131/01/2023
5Ken2100Jan-23B131/01/2023
5Ken1100Jan-23B231/01/2023
5Ken27546Jan-23B231/01/2023

 

Date_Tbl

MthYrQuarterYearQrYrDays in MonthIndexMIS_Date
Mvt.42099Mvt.31031/12/2099
Feb-23120232023,Q128128/02/2023
Jan-23120232023,Q131231/01/2023
Dec-22420222022,Q431331/12/2022
Nov-22420222022,Q430430/11/2022
Oct-22420222022,Q431531/10/2022
Sep-22320222022,Q330630/09/2022

Expected Output:

 

  Mvt.Jan-23Dec-22
  B1B2TOTALB1B2TOTALB1B2TOTAL
IDNAMEST 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_VALTotal
1John900-135765260252851160-1101050100010011003001004001300200150010023533540175215140410550
2James-300-493-793-1500-100-1600-1800-593-2393050500505001001003005438431500200170018007432543
3Job300243543-600433-167-300676376300543843000300543843 3003006001107106004101010
4Mark00054310001543543100015430005431000154354310001543 00000000
5Ken0003076467676307646767675461007646100754676467646764615292754610076467007076161007716

 

stanleycj_0-1681117482799.png

 

 

Looking for expert advise.

Regards,

Stanley 

2 ACCEPTED SOLUTIONS
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1680068901255.png

vxinruzhumsft_2-1680069224067.png

 

 

 

vxinruzhumsft_1-1680068909384.png

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.

 

View solution in original post

Hi @stanleycj 

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

vxinruzhumsft_0-1680075263417.png

Best Regards!

Yolo Zhu

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

 

View solution in original post

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1680068901255.png

vxinruzhumsft_2-1680069224067.png

 

 

 

vxinruzhumsft_1-1680068909384.png

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.

 

Hi @v-xinruzhu-msft ,

 

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.

Hi @stanleycj 

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

vxinruzhumsft_0-1680075263417.png

Best Regards!

Yolo Zhu

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

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors