Power BI YTD Calculations

0
votes

I am trying to do some time based calculations on my budgeting data but struggling to understand where I'm going wrong or if my data structure would even support what I'm trying to do.

enter image description here

As per the image above, this is my raw data. ie. A monthly budgeted and actual total for each cost centre that is being imported from an excel spreadsheet.

I am trying to calculate a YTD budget and YTD Actual figure per cost centre based on the monthly totals. Ideally I would like all of this data displayed in a table that I can then use slicers to segment/pivot.

When using the CALCULATE() function in a measure, I am unable to select my cell value for each date and cost centre.

eg.

YTD Actual = CALCULATE( [Actual MTH] , DATESYTD('Dates'[Date], "30/6"))

returns the error

The value for 'Actual MTH' cannot be determined. Either 'Actual MTH' doesn't exist, or there is no current row for a column named 'Actual MTH'.

Any assistance with getting a greater understanding of the issue here would be appreciated.

Thanks

1
powerbidax
Is [Actual MTH] a column or a Measure? – mxix
It is a column. – MattG

1 Answers

0
votes

Try something like this for your measures:

YTD Actual = TOTALYTD(sum([Actual MTH]),'Dates'[date],ALL('Dates'[date]),"30/6")