Variance between two periods - dynamic

Hi everyone, I am hoping someone would be able to provide some guidance here. I am trying to achieve to things: 1) Calculate the variance between two period in a montn. I want to then ...

Need Help

v-gizhi-msft

6 years ago

Hi,

Please try this calculated column first:

MonthDayCount = DAY(DATE('Table'[FY Year],'Table'[MonthNo]+1,1)-1)

Then try these two measures:

Average PerDay = SUM('Table'[Value])/MAX('Table'[MonthDayCount])

Average PerDay Difference =
VAR a =
    IF (
        MAX ( 'Table'[MonthNo] ) - 1 <> 0,
        CALCULATE (
            [Average PerDay],
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Period] IN FILTERS ( 'Table'[Period] )
                    && 'Table'[FY Year] IN FILTERS ( 'Table'[FY Year] )
                    && 'Table'[MonthNo]
                        = MAX ( 'Table'[MonthNo] ) - 1
            )
        ),
        [Average PerDay]
    )
RETURN
    ( [Average PerDay] - a ) / a

The result shows: