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.
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:
The data in Power BI is in this format given below. Fiscal Year, Fiscal Month and Fiscal Quarter are all text.
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.
Try this calculated column
Incremental = VAR CurrentLine = PREVIOUSMONTH ( TableName[Period] ) RETURN IF ( MONTH ( TableName[Period] ) = 4, TableName[Value (cumulative)], TableName[Value (cumulative)] - CALCULATE ( SUM ( TableName[Value (cumulative)] ), FILTER ( ALL ( TableName ), TableName[Period] = CurrentLine ) ) )
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |