Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table and need to calculate a difference between 2 rows with a condition that it only applies to Agregation Type = All Branches - YTD and it is a diff between fiscal month and fiscal month-1.
In the table I added a column Diff that should show those results. # of rows will grow and is added on a weekly basis.
Link_key | Fiscal Year | Fiscal Month | Fiscal Week | Agregation Type | Applicants | Diff |
2021-1-All Branches - YTD | 2021 | 1 | 4 | All Branches - YTD | 25 840 | |
2021-2-All Branches - YTD | 2021 | 2 | 8 | All Branches - YTD | 50 133 | 24 293 |
2021-3-All Branches - YTD | 2021 | 3 | 13 | All Branches - YTD | 75 089 | 24 956 |
2021-4-All Branches - YTD | 2021 | 4 | 17 | All Branches - YTD | 94 888 | 19 799 |
2021-5-All Branches - YTD | 2021 | 5 | 21 | All Branches - YTD | 117 866 | 22 978 |
2021-6-All Branches - YTD | 2021 | 6 | 26 | All Branches - YTD | 151 612 | 33 746 |
2021-7-All Branches - YTD | 2021 | 7 | 30 | All Branches - YTD | 183 447 | 31 835 |
2021-8-All Branches - YTD | 2021 | 8 | 34 | All Branches - YTD | 217 275 | 33 828 |
2021-9-All Branches - YTD | 2021 | 9 | 39 | All Branches - YTD | 262 469 | 45 194 |
2021-10-All Branches - YTD | 2021 | 10 | 43 | All Branches - YTD | 303 900 | 41 431 |
2021-11-All Branches - YTD | 2021 | 11 | 47 | All Branches - YTD | 343 324 | 39 424 |
2021-12-All Branches - YTD | 2021 | 12 | 52 | All Branches - YTD | 386 621 | 43 297 |
2022-1-All Branches - YTD | 2022 | 1 | 4 | All Branches - YTD | 37 203 | |
2022-2-All Branches - YTD | 2022 | 2 | 8 | All Branches - YTD | 69 594 | 32 391 |
2022-3-All Branches - YTD | 2022 | 3 | 13 | All Branches - YTD | 106 129 | 36 535 |
2022-4-All Branches - YTD | 2022 | 4 | 17 | All Branches - YTD | 139 629 | 33 500 |
2022-5-All Branches - YTD | 2022 | 5 | 21 | All Branches - YTD | 175 917 | 36 288 |
2022-6-All Branches - YTD | 2022 | 6 | 26 | All Branches - YTD | 227 160 | 51 243 |
2022-7-All Branches - YTD | 2022 | 7 | 30 | All Branches - YTD | 269 020 | 41 860 |
2022-8-All Branches - YTD | 2022 | 8 | 34 | All Branches - YTD | 306 487 | 37 467 |
2022-9-All Branches - YTD | 2022 | 9 | 39 | All Branches - YTD | 354 041 | 47 554 |
2021-1-All Branches - MTD | 2021 | 1 | 4 | All Branches - MTD | ||
2021-2-All Branches - MTD | 2021 | 2 | 8 | All Branches - MTD | ||
2021-3-All Branches - MTD | 2021 | 3 | 13 | All Branches - MTD | ||
2021-4-All Branches - MTD | 2021 | 4 | 17 | All Branches - MTD | ||
2021-5-All Branches - MTD | 2021 | 5 | 21 | All Branches - MTD | ||
2021-6-All Branches - MTD | 2021 | 6 | 26 | All Branches - MTD | ||
2021-7-All Branches - MTD | 2021 | 7 | 30 | All Branches - MTD | ||
2021-8-All Branches - MTD | 2021 | 8 | 34 | All Branches - MTD | ||
2021-9-All Branches - MTD | 2021 | 9 | 39 | All Branches - MTD | ||
2021-10-All Branches - MTD | 2021 | 10 | 43 | All Branches - MTD | ||
2021-11-All Branches - MTD | 2021 | 11 | 47 | All Branches - MTD | ||
2021-12-All Branches - MTD | 2021 | 12 | 52 | All Branches - MTD | ||
2022-1-All Branches - MTD | 2022 | 1 | 4 | All Branches - MTD | ||
2022-2-All Branches - MTD | 2022 | 2 | 8 | All Branches - MTD | ||
2022-3-All Branches - MTD | 2022 | 3 | 13 | All Branches - MTD | ||
2022-4-All Branches - MTD | 2022 | 4 | 17 | All Branches - MTD | ||
2022-5-All Branches - MTD | 2022 | 5 | 21 | All Branches - MTD | ||
2022-6-All Branches - MTD | 2022 | 6 | 26 | All Branches - MTD | ||
2022-7-All Branches - MTD | 2022 | 7 | 30 | All Branches - MTD | ||
2022-8-All Branches - MTD | 2022 | 8 | 34 | All Branches - MTD | ||
2022-9-All Branches - MTD | 2022 | 9 | 39 | All Branches - MTD | ||
2022-1-SC Branches - YTD | 2022 | 1 | 4 | SC Branches - YTD | ||
2022-2-SC Branches - YTD | 2022 | 2 | 8 | SC Branches - YTD | ||
2022-3-SC Branches - YTD | 2022 | 3 | 13 | SC Branches - YTD | ||
2022-4-SC Branches - YTD | 2022 | 4 | 17 | SC Branches - YTD | ||
2022-5-SC Branches - YTD | 2022 | 5 | 21 | SC Branches - YTD | ||
2022-6-SC Branches - YTD | 2022 | 6 | 26 | SC Branches - YTD | ||
2022-7-SC Branches - YTD | 2022 | 7 | 30 | SC Branches - YTD | ||
2022-8-SC Branches - YTD | 2022 | 8 | 34 | SC Branches - YTD | ||
2022-9-SC Branches - YTD | 2022 | 9 | 39 | SC Branches - YTD | ||
2022-1-SC Branches - MTD | 2022 | 1 | 4 | SC Branches - MTD | 3 358 | |
2022-2-SC Branches - MTD | 2022 | 2 | 8 | SC Branches - MTD | 3 049 | |
2022-3-SC Branches - MTD | 2022 | 3 | 13 | SC Branches - MTD | 3 308 | |
2022-4-SC Branches - MTD | 2022 | 4 | 17 | SC Branches - MTD | 3 018 | |
2022-5-SC Branches - MTD | 2022 | 5 | 21 | SC Branches - MTD | 3 516 | |
2022-6-SC Branches - MTD | 2022 | 6 | 26 | SC Branches - MTD | 5 445 | |
2022-7-SC Branches - MTD | 2022 | 7 | 30 | SC Branches - MTD | 4 053 | |
2022-8-SC Branches - MTD | 2022 | 8 | 34 | SC Branches - MTD | 4 077 | |
2022-9-SC Branches - MTD | 2022 | 9 | 39 | SC Branches - MTD | 4 283 | |
2022-10-All Branches - YTD | 2022 | 10 | 42 | All Branches - YTD | 382 559 | 28518 |
2022-10-All Branches - MTD | 2022 | 10 | 42 | All Branches - MTD | ||
2022-10-SC Branches - YTD | 2022 | 10 | 42 | SC Branches - YTD | ||
2022-10-SC Branches - MTD | 2022 | 10 | 42 | SC Branches - MTD | 2 548 | |
2022-10-All Branches - YTD | 2022 | 10 | 43 | All Branches - YTD | 391 349 | 37308 |
2022-10-All Branches - MTD | 2022 | 10 | 43 | All Branches - MTD | ||
2022-10-SC Branches - YTD | 2022 | 10 | 43 | SC Branches - YTD | ||
2022-10-SC Branches - MTD | 2022 | 10 | 43 | SC Branches - MTD | 3 344 | |
2022-11-All Branches - YTD | 2022 | 11 | 44 | All Branches - YTD | ||
2022-11-All Branches - MTD | 2022 | 11 | 44 | All Branches - MTD | ||
2022-11-SC Branches - YTD | 2022 | 11 | 44 | SC Branches - YTD | ||
2022-11-SC Branches - MTD | 2022 | 11 | 44 | SC Branches - MTD |
Solved! Go to Solution.
Hi @Sultanista ,
You can try this method:
New column:
Diff =
VAR _curr =
CALCULATE (
SUM ( 'Table'[Applicants] ),
'Table'[Agregation Type] = "All Branches - YTD"
)
RETURN
IF (
[Fiscal Month] = 1
|| 'Table'[Agregation Type] <> "All Branches - YTD"
|| ISBLANK ( 'Table'[Applicants] ),
BLANK (),
_curr
- CALCULATE (
SUM ( 'Table'[Applicants] ),
FILTER (
'Table',
[Agregation Type] = "All Branches - YTD"
&& [Fiscal Year] = EARLIER ( 'Table'[Fiscal Year] )
&& [Fiscal Month]
= EARLIER ( 'Table'[Fiscal Month] ) - 1
)
)
)
The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sultanista ,
You can try this method:
New column:
Diff =
VAR _curr =
CALCULATE (
SUM ( 'Table'[Applicants] ),
'Table'[Agregation Type] = "All Branches - YTD"
)
RETURN
IF (
[Fiscal Month] = 1
|| 'Table'[Agregation Type] <> "All Branches - YTD"
|| ISBLANK ( 'Table'[Applicants] ),
BLANK (),
_curr
- CALCULATE (
SUM ( 'Table'[Applicants] ),
FILTER (
'Table',
[Agregation Type] = "All Branches - YTD"
&& [Fiscal Year] = EARLIER ( 'Table'[Fiscal Year] )
&& [Fiscal Month]
= EARLIER ( 'Table'[Fiscal Month] ) - 1
)
)
)
The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works great! Is it possible to move those results from current place to the corresponding fiscal year and month but aggregation type = All Branches - MTD ?
And having the 1st value of the year (month =1) same as in YTD?
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.
Diff CC =
VAR _previous =
MAXX (
FILTER (
Data,
Data[Fiscal Year] = EARLIER ( Data[Fiscal Year] )
&& Data[Fiscal Month]
= EARLIER ( Data[Fiscal Month] ) - 1
&& Data[Agregation Type] = "All Branches - YTD"
),
Data[Applicants]
)
RETURN
IF (
Data[Agregation Type] = "All Branches - YTD"
&& NOT ISBLANK ( Data[Applicants] ) && NOT ISBLANK ( _previous ),
Data[Applicants] - _previous
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |