cancel
Showing results for
Did you mean:

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

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...