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 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   monthCosts
Planning jan10
Actualsjan12
Planning feb15
Actualsfeb 
Planning mrt15
Actualsmrt 
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
     

5 Replies

  • 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.

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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'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