Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi PowerBI Community,
I have dataset that contains Fiscal_Year, Month, Year, Sale, Sale_In_Each_Month and Desired_Result columns. It's a dummy sales data with cumulative sales in column D (Sale). The fiscal year starts from April until March next year. I am calculating sales in each month (column E) using the following formula:
Calculation1 = SUM('Table'[Sale])
Calculation 2 = CALCULATE([Calculation 1],DATEADD('Calendaer'[Date],-1,MONTH))
Calculation 3 = [Calculation 1] - [Calculation 2]
Sales in each month will be cumulative sale of that month subtracted by the cumulative sale of last month. For example, Sale in each month for May would be = Cumulative Sale in May - Cumulative Sale in April.
Above calculations are working as expected but I need to exclude April month from this calculation because fiscal starts from April and it should show the cumulative sale rather than substraction. For April, the sale in each month should be according to desired column.
The calculations are not resetting when the fiscal starts (April). Please suggest.
Solved! Go to Solution.
@PS0304 , Change calculation three as
Calculation 3 =
If(month(max('Calendaer'[Date])) =4, [Calculation 1] - [Calculation 2], [Calculation 1])
@PS0304 , Change calculation three as
Calculation 3 =
If(month(max('Calendaer'[Date])) =4, [Calculation 1] - [Calculation 2], [Calculation 1])
Thank you so much @amitchandak .
I just changed the calculation to
Calculation 3 =
If(month(max('Calendaer'[Date])) =4, [Calculation 1], [Calculation 1] - [Calculation 2])
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |