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

Kolumam

 

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

 

try this, but, you need to fix the date column as I mentioned in your other thread.

 

 

 

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

Hi

 

Cumulative is showing wrong values. Please check below.

 

 

 

  • 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] ) )
        )