Forum Discussion

laehatlav's avatar
laehatlav
Frequent Visitor
6 years ago

calculate Sum value from specific monthly value

Hello Fellow Power BI,

Need help in calculating the sum value for Monthly Goal column based on specific date period. I would like to sum the value of the Montly Goal value based on the shown Cost value for specific time period select. Thank you for any suggestion.

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    laehatlav can you share how you data tables looks like and what is the relationship between tables. without knowing the model, it is not easy to provide the DAX measure/solution.

    • laehatlav's avatar
      laehatlav
      Frequent Visitor
      6 years ago

      Here is the screenshot of my excel table and source data table. Thank you!

       

      excel table of Monthly goal value:

       

      Source table:

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        laehatlav it is pretty straight forward if you have correct data model, you need to add a date and branch dimension in your model and have relationship with your goal and sales table and then use date/branch column from dimension tables and sum from goal and cost table, from there everything will flow super easy.

         

        Data modelling is critical component to work with Power BI and it takes away lot of work of writing complex DAX expressions and this is one of the example for good data model.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, laehatlav 

     

    Based on your description, I created data to reproduce your scenario. I hope it can help you.

    Table:

     

    DateTable(a calculated table):

     

    DateTable = CALENDARAUTO()

     

     

    Relationships between two tables.

     

    Then you may create a measure as follows.

     

    Monthly Goal = 
IF(
    MAX('Table'[Branch Name]) = BLANK(),
    BLANK(),
    CALCULATE(
        SUM('Table'[Cost]),
        ALLSELECTED('Table')
    )
    
)

     

     

    Then you may use the Date column from DateTable to filter the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.