Forum Discussion

corange's avatar
corange
Post Patron
6 years ago
Solved

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's avatar
    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:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
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:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto