Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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
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.
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
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |