topic Re: Calculate Previous Month Value But It Will Reset Every Fiscal Year in DAX Commands and Tips

Calculate Previous Month Value But It Will Reset Every Fiscal Year

Anonymous — Fri, 14 Jun 2024 05:19:39 GMT

Hi all,

I would appreciate your help calculating the previous month's value, but it needs to reset the previous month's value to zero if the fiscal year also changes. In the example below, the value in January 2023 is expected to be 0, and the value of the following months is already correct, but I don't know how to make a DAX with results like the table below. All I need is for the previous month's value is always reset to 0 every May, considering the fiscal year is from May to April.

Expected result:

Thank you!

Re: Calculate Previous Month Value But It Will Reset Every Fiscal Year

bhanu_gautam — Fri, 14 Jun 2024 05:49:15 GMT

Hi Anonymous , Create a date table first and add following columns in it Date, Year , Month, Fiscal year, Fiscal month.

Then create a calulated column in main table using formula

PreviousMonthValue =
VAR CurrentDate = 'Sales'[Date]
VAR CurrentFiscalYear = YEAR(CurrentDate) + IF(MONTH(CurrentDate) >= 5, 1, 0)
VAR PreviousMonth = EOMONTH(CurrentDate, -1)

RETURN
IF (
MONTH(CurrentDate) = 5,
0,
CALCULATE(
SUM('Sales'[Value]),
FILTER(
'Sales',
'Sales'[Date] = PreviousMonth &&
(YEAR('Sales'[Date]) + IF(MONTH('Sales'[Date]) >= 5, 1, 0)) = CurrentFiscalYear
)
)
)

Re: Calculate Previous Month Value But It Will Reset Every Fiscal Year

AntrikshSharma — Fri, 14 Jun 2024 07:57:31 GMT

Anonymous

Previous Month Value = IF ( ISINSCOPE ( 'Date'[Fiscal Year Number] ), -- Removes the Grand Total IF ( MAX ( 'Date'[Fiscal Month Number] ) = 1, 0, -- Just show 0 when month is May CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ), VALUES ( 'Date'[Fiscal Year] ) -- Only perform DATEADD for current year, -- returns NULL for first month next year ) ) )

you can improve the above code with some variables for readability

Previous Month Value 2 = VAR IsFirstMonth = MAX ( 'Date'[Fiscal Month Number] ) = 1 VAR GroupingByYear = ISINSCOPE ( 'Date'[Fiscal Year Number] ) VAR PreviousMonthValue = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ), VALUES ( 'Date'[Fiscal Year] ) ) VAR Result = IF ( GroupingByYear, IF ( IsFirstMonth, 0, PreviousMonthValue ) ) RETURN Result

can also use OFFSET

Previous Month Value OFFSET = VAR IsFirstMonth = MAX ( 'Date'[Fiscal Month Number] ) = 1 VAR GroupingByYear = ISINSCOPE ( 'Date'[Fiscal Year Number] ) VAR PreviousMonthValue = CALCULATE ( [Total Sales], OFFSET ( -1, ALL ( 'Date'[Month], 'Date'[Fiscal Month Number], 'Date'[Fiscal Year Number] ), ORDERBY ( 'Date'[Fiscal Month Number], ASC ), PARTITIONBY ( 'Date'[Fiscal Year Number] ) ) ) VAR Result = IF ( GroupingByYear, IF ( IsFirstMonth, 0, PreviousMonthValue ) ) RETURN Result