Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need your help with a DAX formula

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 wh...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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
     
v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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