I’m wanting to calculate a stretch target that the Board of Directors has requested in a company I’m CFO for. I can get the measure to work on a graph because that filters everything by date. However, I want to display the measure as a card. And that’s causing me issues. Essentially I’m looking for a DAX formula to do this for me where the budgeted figures into the future are ignored.
I’ve been asked to target an additional Operating Expenditure Savings for the year of $XXX. I’ve got live data coming into the model from an Azure Database. That Database also holds the agreed budget as well. I’m only wanting to calculate the YTD savings to the latest month end date because if I have it through to the current date it’s essentially nonsense because the month hasn’t ended so the savings are artificially inflated. To put it another way, if we’re in September, I only want to show the YTD Savings through to the end of August.
The measures I’ve got essentially go Cumulative Actual Opex less Cumulative Budgeted Opex = Savings. That works fine on a graph as I can zero out future months on the graph with an if statement. However it doesn’t work on a card because I’ve got Budgeted Opex all the way through to the end of the year but I obviously don’t have Actual Opex. This means my savings make no sense because its telling me we’ve spent $0 in future months so the savings are huge … which is nonsense as I said above.
So I’m looking for a Dax formula that tells the calculation to ONLY add up all actual and budgeted expenditure up to the end of the last month and ignore everything else after that. This is what is working on a graph, but is proving no good in a card:
OpEx Saved to Last Month End = IF([Operating Expenses]=0,"",
CALCULATE([YTD Savings],DATEADD(‘Date Table’[Date],-1,MONTH)))
I know this probably sounds easy, but it’s got me stumped today!
Thanks in advance