Variance between two periods - dynamic

[corange]

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. 

[v-gizhi-msft · Accepted solution]

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

[amitchandak]

@corange 

Can see using Rank can help. The way I have used for weeks. Hope you have calendar with periods

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490