Forum Discussion

Ecobat's avatar
Ecobat
Regular Visitor
2 years ago
Solved

Running total with multi criteria

I new to BI so i'm in the early stages of coding.

 

I'm have an issue with my running monthly total.

The running total should look at the Cost centre then by Cost Element then by date but the total is have and issue when i have more that one cost centre or cost element.

 

CALCULATE(
SUM('GDM COST_SUMMARY_FACT_VR'[ACTUAL_VALUE_CO_CURR]),
        FILTER(
            ALLSELECTED('GDM COST_SUMMARY_FACT_VR'),
            'GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID]
            && 'GDM COST_SUMMARY_FACT_VR'[COST_ELEM_ID]
            && 'GDM COST_SUMMARY_FACT_VR'[Mateix_Date] <= MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date])
            && year('GDM COST_SUMMARY_FACT_VR'[Mateix_Date])=year(MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date])
            )))

 

Thank you for you help

Ecobat

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Ecobat ,

     

    Hope you have a nice day.

     

    Please try:

     

    CALCULATE(
    SUM('GDM COST_SUMMARY_FACT_VR'[ACTUAL_VALUE_CO_CURR]),
    FILTER(
        ALLSELECTED('GDM COST_SUMMARY_FACT_VR'),
        'GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID] == MAX('GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID])
        && 'GDM COST_SUMMARY_FACT_VR'[COST_ELEM_ID] == MAX('GDM COST_SUMMARY_FACT_VR'[COST_ELEM_ID])
        && 'GDM COST_SUMMARY_FACT_VR'[Mateix_Date]<= MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date])
        && YEAR('GDM COST_SUMMARY_FACT_VR'[Mateix_Date]) == YEAR(MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date]))
    )
)

     

    Please check the table and field names for spelling errors.

     

    The DAX formula should be corrected to do what you want.

     

    If your problem cannot be solved successfully, please provide error details or a data sample set with personal privacy information and sensitive information removed. Please feel free to contact me at any time.

     

    Warm Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ecobat ,

     

    Hope you have a nice day.

     

    Please try:

     

    CALCULATE(
    SUM('GDM COST_SUMMARY_FACT_VR'[ACTUAL_VALUE_CO_CURR]),
    FILTER(
        ALLSELECTED('GDM COST_SUMMARY_FACT_VR'),
        'GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID] == MAX('GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID])
        && 'GDM COST_SUMMARY_FACT_VR'[COST_ELEM_ID] == MAX('GDM COST_SUMMARY_FACT_VR'[COST_ELEM_ID])
        && 'GDM COST_SUMMARY_FACT_VR'[Mateix_Date]<= MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date])
        && YEAR('GDM COST_SUMMARY_FACT_VR'[Mateix_Date]) == YEAR(MAX('GDM COST_SUMMARY_FACT_VR'[Mateix_Date]))
    )
)

     

    Please check the table and field names for spelling errors.

     

    The DAX formula should be corrected to do what you want.

     

    If your problem cannot be solved successfully, please provide error details or a data sample set with personal privacy information and sensitive information removed. Please feel free to contact me at any time.

     

    Warm Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Ecobat 

    Can you please try below code?

     

    CALCULATE(
    SUM('YourTable'[Amount]),
    FILTER(
        ALL('YourTable'),
        && 'YourTable'[CostCentre] = EARLIER('YourTable'[CostCentre])
        && 'YourTable'[CostElement] = EARLIER('YourTable'[CostElement])
        && 'YourTable'[Date] <= EARLIER('YourTable'[Date])
    )
)

     

    Please share usable data in share format for further assistant


    If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    www.MyAccountingTricks.com 
    https://www.youtube.com/MyAccountingTricks

     

    • Ecobat's avatar
      Ecobat
      Regular Visitor
      2 years ago

      Hi Pijush

       

      Sorry no this does not work

       

      I get the following error

       

      The syntax for '&&' is incorrect. (DAX(CALCULATE(SUM('GDM COST_SUMMARY_FACT_VR'[ACTUAL_VALUE_CO_CURR]), FILTER( ALL('GDM COST_SUMMARY_FACT_VR'), && 'GDM COST_SUMMARY_FACT_VR'[COST_CNTR_ID] = EARLIER('GDM COST_SUMMARY_FACT_VR'[COST

       

      I am also using Matrix instead of table