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 tota...
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!

PijushRoy's avatar
PijushRoy
Community 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