Forum Discussion

Kolumam's avatar
Kolumam
Icon for Post Prodigy rankPost Prodigy
8 years ago
Solved

Need help in formula

Hi  All,   I need a cumulative sum of size for each month from the start July 2016 to August 2018. How do I achieve this using DAX ? 
General Comment
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    8 years ago

    Kolumam,

    Create a calendar table in Power BI Desktop following the guide in this blog, create relationship between the date field of Date table and COD field of Project table. Then change your DAX to the following, drag date field of Date table and the measure to your chart.

    Running Totals =
CALCULATE (
    SUM ( Project[Operating Size] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)


    Regards,
    Lydia

rocky09's avatar
rocky09
Icon for Solution Sage rankSolution Sage
8 years ago

Can you post sample data? (copy directly from excel to here). I can test it on my system.

popov's avatar
popov
Icon for Resolver III rankResolver III
8 years ago

Kolumam
Try this formula:

Running Totals =
CALCULATE (
    SUM ( 'Table1'[Size(MWP)] ),
    FILTER (
        ALLEXCEPT ( 'Table1', 'Table'[COD] ),
        'Table1'[COD] <= MAX ( 'Table1'[COD] )
    )
)

  • popov's avatar
    popov
    Icon for Resolver III rankResolver III
    8 years ago

    Sorry, formula doesn't correct. Try this formula:

    Running Totals =
    CALCULATE (
        SUM ( 'Table1'[Size(MWP)] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[COD] <= MAX ( 'Table1'[COD] ) )
    )

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Kolumam

       

      Another way could be to use INONORAFTER

       

      Measure =
VAR mymax =
    MAX ( Table1[COD] )
RETURN
    CALCULATE (
        SUM ( Table1[Size(MWP)] ),
        ISONORAFTER ( Table1[COD], mymax, DESC )
    )
      • Kolumam's avatar
        Kolumam
        Icon for Post Prodigy rankPost Prodigy
        8 years ago

        Hi All, 

         

        The issue with the below bar graph is that for the months with no COD it is not taking the same number and instead it is blank.

         

        How do I fix this ?