Forum Discussion
Subtraction from different rows
Hi All,
I need to calculate the following in incremental pattern.
Period | Value (cumulative) | Value (incremental) | Remarks |
Apr-17 | 100 | 100 | For the start of each FY, meaning Apr of every year, the incremental value= value (Cumulative) |
May-17 | 250 | 150 | For subsequent months, use vaIue of that month and mius off value of previous month |
Jun-17 | 400 | 150 |
|
Jul-17 | 600 | 200 |
|
Aug-17 | 900 | 300 |
|
Sep-17 | 1800 | 900 |
|
Oct-17 | 2000 | 200 |
|
Nov-17 | 2500 | 500 |
|
Dec-17 | 2600 | 100 |
|
Jan-18 | 3000 | 400 |
|
Feb-18 | 3200 | 200 |
|
Mar-18 | 3200 | 0 |
|
Apr-19 | 140 | 140 |
Now the value in data source is cumulative.To get incremental value:
- Value of Jun 2017 refers to value from 2017 Apr 2017 to Jun 2017. In sample below value is 400. This is Q1 value
- Value of Sep 2017 refers to value from Apr 2017 to Sep 2017. In sample below value is 1800. To get Q2 value, use 1800-400 = 1400
- Value of Dec 2017 refers to value from Apr 2017 to Dec 2017. In sample below value is 2600. To get Q3 value, use 2600-1800 = 800.
The data in Power BI is in this format given below. Fiscal Year, Fiscal Month and Fiscal Quarter are all text.
5 Replies
Are you simply after a Financial YTD that resets every April?
If so the following calculation might be close. It does rely that your [Period] column is DateTime, rather than Text. Ideally this will be the first day in each month, so Apr17 will actually be 2017-04-01
Value Cumulative = TOTALYTD(SUM('Table3'[Value]),'Table3'[Period],"30/3")This formula is one way you can derive the gap backwards
Subtract Gaps = [Value Cumulative] - CALCULATE([Value Cumulative], FILTER( ALL('Table3'[Period]), [Period] = var d1 = min('Table3'[Period]) var myStartOfMonth = DATE(year(d1),month(d1),1) - 1 RETURN DATE(Year(myStartOfMonth),Month(myStartOfMonth),1) ) )This gives me the same result as cumulative values.
