DAX Sum(Value) based on Previous calculation Months

Question

HI all I'm trying to create a % variance for Budget to Actuals, While I can do this I'm getting an issue with the rollup. Actual data is complete July-October Budget is for the Complete Year (Fiscal)

So using Actual:=Calculate(Sum([Actual]),Version[VersionID]=1,Filter(DimTime,[Year]=2015)) I get the total by month with Quarter / Year rollup for the Actuals

For Budget I want the same thing however I can't get it filtered to only Sum the Same time period of the actuals. SO the Year and Quarter rollup I always receive the Whole year/quarter data.

Wrong
Time Actual Budget VTB

2015 566913 1667972 194%

Q1 400863 399702 0%

Jul 131092 137071 5%

Aug 126736 127712 1%

Sep 143035 134920 -6%

Q2 166050 393531 137%

Oct 166050 142959 -14%

Correct
Time Actual Budget VTB

2015 566913 542661 194%

Q1 400863 399702 0%

Jul 131092 137071 5%

Aug 126736 127712 1%

Sep 143035 134920 -6%

Q2 166050 142959 137%

Oct 166050 142959 -14%

I can capture the Max(TimeID) as it relates to the actuals in this case 201410 as a value.

Budget:=Calculate(Sum([Budget]),Version[VersionID]=1,Filter(DimTime,[Year]=2015)) Would Like Budget:=Calculate(Sum([Budget]),Version[VersionID]=1,Filter(DimTime,[TIMID]<= Max(TIMEID))

I just can't seem to apply it to the sum of Budget values

Thanks

Phil

Answer 1

Using following technique you can see above required Report, Only caveat here is that you would be able to see rows for either Year or Quarter or Month.

Assuming you have Budgets by Month. You will have to create a new table for "Month" with these columns (MonthName,MonthNumber,Year). Add a calculated column in your DimTime to refer to appropriate row in newly created Month table. Also relate your Budget table to this newly created Month table.

Now when you use this Month.MonthName on Rows in PivotTable, you should see desired result.