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 display the job count for each period and the variance in a matric table and this for the entire FY. This is how my table look like: 

 

 

* Note - in the same matrix, I have also Revenue displayed. The data set is structure in a way where I have category broken down into Job Count and Revenue and the value is grouped under one column. 

 

2) I would like to calculate the average spend per day for each period and calculate the difference with the historical ave spend per day. Here as well, I wish to use a matrix to display my result for the entire FY. 

 

Please let me know if you can help. 

 

Thank you. 

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

3 Replies