Calculate Previous Month Value But It Will Reset Every Fiscal Year

[Anonymous]

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!

[AntrikshSharma · Accepted solution]

@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

[bhanu_gautam]

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
)
)
)