CODEWITHSUNDEEP
powerbidaxvisualizationpowerbi-desktoppowerbi-custom-visuals
Francesco Mantovani
Francesco Mantovani

Reputation: 12327

Power BI :: calculate delta for baseline of a specific date

SCENARIO: Azure App Services have high cost on the cloud.

That's why at the end of November 2022 we put in place a cost reduction. As per the 30 of November 2022 the costs were at 83.63 per day. The day after they dropped to 48.22 per day:

enter image description here

GOAL: I would like to calculate the delta to highlight the success of the cost reduction

This is the DAX that I use to calculate the costs of App Services:

Savings App Service  = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), 
FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" 
&& CONTAINSSTRING('Usage details'[meterName.1], "v2") 
&& ('Usage details'[subscriptionName] = "devqa"
|| 'Usage details'[subscriptionName] = "presales"
|| 'Usage details'[subscriptionName] = "sandbox")
 ))

It's all about line 2: CALCULATE(SUM('Usage details'[costInBillingCurrency]), this, for now, calculate the actual costs.

What I need to do now is to calculate a fixed baseline based on the date of 30 November 2022. The code should look like:

Realised Testing = 
CALCULATE(sum('Usage details'[costInBillingCurrency]),FILTER(ALL('Usage details'[date].[Date]) = "30/11/2022") - SUM('Usage details'[costInBillingCurrency])
FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" 
&& CONTAINSSTRING('Usage details'[meterName.1], "v2") 
&& ('Usage details'[subscriptionName] = "devqa"
|| 'Usage details'[subscriptionName] = "presales"
|| 'Usage details'[subscriptionName] = "sandbox")
 ))

But I receive an error that says The Syntax for 'FILTER' is incorrect:

enter image description here

What am I doing wrong?

If creating a baseline based on the 30 of November is not possible also calculating the AVG of the whole month of November is a valid solution for me.

Thank you.

EDIT: Hi David I have updated 2 new files

While your code it looks like it's working the numbers are decreasing every month.

enter image description here

In February we have definitely saved more money than in January and December. But from the calculation it looks like in February we have saved less than December. I don't understand why because December over November looks quite realistic.

Upvotes: 2

Views: 707

Answers (1)

davidebacci
davidebacci

Reputation: 30289

UPDATED

enter image description here

enter image description here

Measure 1

Fixed Amount = 
 var fixedAmount = CALCULATE(SUM(Data[Cost]), FILTER(ALL(Data), Data[UsageDate]= DATE(2022,11,30)))

 RETURN  IF(NOT(ISBLANK(SUM(Data[Cost]))) && MIN(Data[UsageDate])>= DATE(2022,11,30), fixedAmount)

Measure 2

Delta = IF(NOT(ISBLANK(SUM(Data[Cost]))) && MIN(Data[UsageDate])>= DATE(2022,11,30), SUMX(VALUES(Data[UsageDate]),[Fixed Amount] - CALCULATE(SUM(Data[Cost])) ))

Upvotes: 1

Related Questions