Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Realized cumulative calculation

Hello everybody,

Here I have a DAX function which calculate realized actions on the month :

DX-Réceptionné mensuel =
CALCULATE(
SUM('AERO - Achats'[Réalisé])/1000,
'Dim - Versions'[PQM - Année Mois] = MAX('Dim - Versions'[PQM - Année Mois])
)
 
PQM - Année Mois give me the Year-Month value :
The measure gives me the blue bars on the graph at the end.
I search now to calculate the cumulative sum of these realized actions over the months. Here is my measure :
CALCULATE(
SUM('AERO - Achats'[Réalisé])/1000,
ALLSELECTED('Dim - Versions'[PQM - Année Mois]),
'Dim - Versions'[PQM - Année Mois] <= MAX('Dim - Versions'[PQM - Année Mois])
)
It gives me the blue line on the graph
 

As you can see, it doesn't give the expected result. I tried different things but impossible to find the solution.

Can somebody can give me help to resolve this problem ?

 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please update the formula of your measure for culmulative calculation as below and check if it can return the expected result...

    Culmulative =
CALCULATE (
    SUM ( 'AERO - Achats'[Réalisé] ) / 1000,
    FILTER (
        ALLSELECTED ( 'Dim - Versions' ),
        'Dim - Versions'[PQM - Année Mois] <= MAX ( 'Dim - Versions'[PQM - Année Mois] )
    )
)

    If the above one can't help you, please provide some raw data in your table 'AERO - Achats' with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please update the formula of your measure for culmulative calculation as below and check if it can return the expected result...

    Culmulative =
CALCULATE (
    SUM ( 'AERO - Achats'[Réalisé] ) / 1000,
    FILTER (
        ALLSELECTED ( 'Dim - Versions' ),
        'Dim - Versions'[PQM - Année Mois] <= MAX ( 'Dim - Versions'[PQM - Année Mois] )
    )
)

    If the above one can't help you, please provide some raw data in your table 'AERO - Achats' with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This process/patern will work.

    1. Create a Calendar Table with calculated column formlas for Year, Month name and Month number.  Sort the Month name by the Month number.
    2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
    3. To your visual, drag Year and Month from the Calendar Table
    4. Write these measures

    Total = divide(SUM('AERO - Achats'[Réalisé]),1000)

    Total YTD = calculate([Total],datesytd('Calendar'[date]))

    Hope this helps.