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 ,

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

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.