Need your help with a DAX formula

[Anonymous]

Hello,

I'm new to DAX. I want to sum the annual costs. Meaning if its February the meassure should take actuals for January and for the remaining months the planning category. Is there someone who can help me. The following table is a simple example of my datamodel. In the actual model i have more than one planning category. The user will select the desired planning category in a slicer.

Regards,

Paul

Categroy	month	Costs
Planning	jan	10
Actuals	jan	12
Planning	feb	15
Actuals	feb
Planning	mrt	15
Actuals	mrt

[v-danhe-msft]

Hi @Anonymous ,

What is your desired result? If I select 'feb' in a slicer, it will sum 12(jan actual)+15(feb palning)+15(march planing), right?

Regards,

Daniel He

[Anonymous]

@v-danhe-msft Did you know a way how to create a meassure for this problem?

If there is an actual avaidable, it should sum the actuals if there is no actual it should sum the planning figures. Result: actual jan + planning feb + planning march.

Regards,

Paul

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below steps and I suggest you use number to show your month:

Create a distinct table for month column:

New Table = DISTINCT('Table1'[Month])

Create below measure:

Measure = CALCULATE(SUM(Table1[Costs]),FILTER('Table1','Table1'[Month]<=SELECTEDVALUE('New Table'[Month])&&'Table1'[Categroy]="Actuals"))+
CALCULATE(SUM(Table1[Costs]),FILTER('Table1','Table1'[Month]>=SELECTEDVALUE('New Table'[Month])&&'Table1'[Categroy]="Planning"))

Result(Use the [Month] in new table as slicer):

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

@v-danhe-msft The option with a slicer is working perfect. Problem is my enduser doesn't want to use a slicer.... I modified the dax a little bit.... Now i'm stuck with the following error "A function filter has been used in a true/false statement that is used as a filter expression"

I used the following Dax Coding:

Annual Costs = Calculate(sum('Main Table'[Actual Basic Costs]), FILTER('Main Table','Main Table'[Month] < 'date'[current month])&&'Main Table'[Category]="Loaded Actuals (without manual input)")+
Calculate(sum('Main Table'[Actual Basic Costs]), FILTER('Main Table','Main Table'[Month] >= 'Date'[current month])&&'Main Table'[Category]<>"Loaded Actuals (without manual input)")

This way i would like to exclude the slicer option.....

Many thanks for your help!!

Regards,

Paul

[Anonymous]

Yes,

option 1 if the user selects Feb in a slicer the result should be actuals for January and planning for the rest of the year. The enduser wants to predict the annual costs. 

Option 2: the formula sums up all actual months (when avaidable) if not avaidable use the planned costs to get the predicted annual costs.