Budget and actuals at different granularity

[DavidWaters100]

Hi,

I have the age-old problem with visuals looking at actuals and budgets which are at different levels of granularity.  I have tried looking at old posts about this but none seemed to have the extra complexity of a YTD and MTD switch.

Throughout my reports there is a YTD and MTD switch per below, using DATESMTD and DATESYTD. A calendar table is joined to a start date in the “actual” table and works fine. There are 3 main column types – Class, Subclass and Location for which budgets are measured.

My problem is trying to return the budget numbers against the 3 types when a visual is aggregating the actual table which has entries for every single day, but the budget table only has entries per month for each of the three types.  The measure for the budget also needs to respond to YTD or MTD but is only at month level. 

I have tried many solutions but can’t get this to work! Any help greatly appreciated.

Below is the YTD / MTD switch:

YTD MTD = switch(SELECTEDVALUE(Period[Period]),"YTD",[YTD],"One Month",[MTD])

YTD = Calculate(sum('Power BI'[start date]),DATESYTD('Calendar Table'[Date]))

MTD = Calculate(sum('Power BI'[start date]),DATESMTD('Calendar Table'[Date]))

[amitchandak]

Can you convert month into a month start or month-end date like

Say month is - Jan-2020

Date = "01-" & [Month] // Change data type to date

Say month 2020-10

Date = date(year(left([Month],4)),month(right([Month],2)),1)

[DavidWaters100]

The start date has to have the relationship to calendar date as there are other metrics like week number (ie not month start only)

[amitchandak]

If data is at the month level. Then the start date of the month should join with the date of the calendar.

Now as the calendar has all dates. It might end up showing data on the first data as well as the first week. That you take care while display not to show budget at day or week level. Another way is allocation at the day level

Refer

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

[DavidWaters100]

The actual table is at date level, the budget table is at month level.  I have now posted a sample file to Excel online to show the content of each table

[amitchandak · Accepted solution]

Check solution at :https://www.dropbox.com/s/9k8xppzj87geo4d/month_yearly.pbix?dl=0

[DavidWaters100]

Hi thanks for your solution - it works with the sample data but my table relationships are quite different from this basic sample data.  I don't think this solution will work for me without major changes...

[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

[DavidWaters100]

Sorry here is link to sample data in Excel with expected results

https://1drv.ms/x/s!AgZEuf7Vkrdog3bOQu53BhWaLP59?e=VTiZbh

Password is Ruby100