cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

3 REPLIES 3
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:

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

Super User

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

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors