Forum Discussion

MH3's avatar
MH3
Helper V
6 years ago

Commulative Total Not Working or Broken

Hello,

I have a Measure for Revenue, and I was trying to calculate the Commulative Total, but it's not displaying correct values.

as the formula for the Commulative total I used is:

Cummulative = 
CALCULATE(
	Sales[Revenue],
	FILTER(
		ALLSELECTED('Calendar'),
	'Calendar'[Date] <= Max('Calendar'[Date])))


See the Screen Shot

 

For the year 2014, Month January 

Day 1: Revenue is 3,300 , Commulative is 3,300 which is correct!

Day 2: Revenue is 694,200, Commulative is 793,800 which is WRONG!!

 

The whole commulative is showing incorrect values.

Kindly help!!

Need Help
Show and Tell

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    MH3 , I am assuming this a measure like this

    Cummulative =
    CALCULATE(
    sum(Sales[Revenue]),
    FILTER(
    ALLSELECTED('Calendar'),
    'Calendar'[Date] <= Max('Calendar'[Date])))

     

    Also make sure year, month or any other time related stuff is taken from Calendar in visual

    • MH3's avatar
      MH3
      Helper V
      6 years ago

      Hello amitchandak ,

       

      Sales[Revenue] is already a measure does it needs to be in SUM function? and I have used this formula but still I am not getting correct results.

       

      Help!

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi MH3 ,

     

    We can use the following measure to meet your requirement.

     

    Cummulative = 
CALCULATE(
            SUMX(VALUES('Calendar'[Date]),'Sales'[Measure revenue]),
            FILTER(
                        ALLSELECTED('Calendar'),
            'Calendar'[Date] <= Max('Calendar'[Date])))

     

    Then result like this,

     

     

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.