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.

     

     

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