Forum Discussion

Kolumam's avatar
Kolumam
Post 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

popov's avatar
popov
Resolver 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
Community 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
    Post 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 ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      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