Dynamic Measure calculation using Filters

[Anonymous]

Hi, I am new to Power Bi and struggling over a measure.

I want to compare a month verses YTD up to previous month. So if my current month is period 10 (Jan), I want a dynamic measure that gives periods 1 to 9. Our financial year runs from April to March, Period 1 = April.

My page filter is 'Date'[Fiscal Period No] = 10

TY Rev YTD-1M = CALCULATE(SUM(Sales[Value]),'Date'[Fiscal Period No]<10), returns the correct result but is not dynamic (related tio the filter)

Any help appreciated.

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following measure to calculate rolling total based on current selection:

TY Rev YTD-1M =
VAR selected =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( Date ) )
RETURN
    CALCULATE (
        SUM ( Sales[Value] ),
        FILTER ( ALLSELECTED ( Sales ), Sales[Date] <= selected )
    )

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng