Time Intelligence Functions for Budget Vs Actual at different granularity of time

[Anonymous]

Hi Everyone,

I am working on Budget Vs Actual Analysis and below is the sample/basic dataset in use.

Actuals - Stores company sales at date level

Budget - Stores budgeted sales at Month Level

Calendar - Complete calendar with lowest granularity as date.

For now using a gregorian calendar, but may shift to fiscal calendar wherein the first day may not be "1".

How do I calculate YTD,MTD , Previous year , Previous Month Budget using Time intelligence Functions considering the Period level at which Budget is stored?

Thanks

Varshada

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

please check the below picture and the sample pbix file's link down below.

It is one of many ways to deal with a budget table that has a different granularity in date related column.

All measures are in the sample pbix file.

https://www.dropbox.com/s/m1g4351ciwh1cho/varshada.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

please check the below picture and the sample pbix file's link down below.

It is one of many ways to deal with a budget table that has a different granularity in date related column.

All measures are in the sample pbix file.

https://www.dropbox.com/s/m1g4351ciwh1cho/varshada.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim ,

I tried the DATESQTD , YTD functions on Budget Total allocated Daily , and it calculates correct values for the budget sales.Any specific reason , we may not want to go for those functions and instead use the calculation for YTD/MTD mentioned in your file

Thanks

Varshada

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

Mark the calendar as date table.

Then create measures:

MTD = 
CALCULATE(
    SUM(Budget[Sales]),
    DATESMTD('Calendar'[Date])
)

YTD = 
CALCULATE(
    SUM(Budget[Sales]),
    DATESYTD('Calendar'[Date])
)

Previous Month = 
CALCULATE(
    SUM(Budget[Sales]),
    PREVIOUSMONTH('Calendar'[Date])
)

Previous Year = 
CALCULATE(
    SUM(Budget[Sales]),
    PREVIOUSYEAR('Calendar'[Date])
)

Month_Previous_Year = 
CALCULATE(
    SUM(Budget[Sales]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Jihwan_Kim ...Going through the solution.I have understood most of it..Even tried Previous and Next Month DAX functions...Results in expected values.

Ex. 

Previous Month = CALCULATE([Budget Total Daily Allocate Total Fix],NEXTMONTH(Dates[Date]))

I shall try the solution in my pbix and let you know.

Can we not use DATESYTD , DATESMTD for MTD and YTD Budget Sales?

Thanks 

Varshada