Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
corange
Post Patron
Post Patron

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: 

 

Capture.PNG

 

* 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. 

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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:

3.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

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:

3.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

amitchandak
Super User
Super User

@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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.