Forum Discussion

Angel's avatar
Angel
Resolver III
6 years ago
Solved

Difference betweeen two cummulative values

Hi, everyone   I have been struggled with the phormula to get a difference between two cummulative values and I'm stuck. I want to show in a card the difference between two cummulative values depen...
cummulative
dates
Difference
Need Help
  • Angel's avatar
    Angel
    6 years ago

    Hi, Anonymous , Anonymous 

     

    First of all, thanks for the quick responses. It have been useful. 

     

    Anonymous. I tried phormula you said and it shows an error. I think it's because Cummulative Quantity is a measure in the model, instead of a colum.

     

     

     

     

     

    Anonymous. The phormula you said works, except for the phormula to calculate 'Cumul Qty'. 

    So, I have changed phormula por Cumul Qty and it works.

     

     

    Cummulative Quantity = 

CALCULATE(SUM(Sales[Quantity]);FILTER(ALL('Calendar'); 'Calendar'[Date] <= MAXX('Calendar';'Calendar'[Date])))

     

     

     

    Diff = 
	[Cummulative Quantity]
	- CALCULATE(
		[Cummulative Quantity];
		FIRSTDATE( 'Calendar'[Date] )
	)

     

     

    And it works.....

     

    Thanks

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
// Calendar must be marked as the Date table
// in the model.

[Cumul Qty] = // that's faster and simpler
	CALCULATE(
		SUM( Sales[Quantity] ),
		Calendar[Date] <= MAX( Calendar[Date] )
	)

[Diff] =
	[Cumul Qty]
	- CALCULATE(
		[Cumul Qty],
		FIRSTDATE( Calendar[Date] )
	)

Best

D

  • Angel's avatar
    Angel
    Resolver III
    6 years ago

    Hi, Anonymous , Anonymous 

     

    First of all, thanks for the quick responses. It have been useful. 

     

    Anonymous. I tried phormula you said and it shows an error. I think it's because Cummulative Quantity is a measure in the model, instead of a colum.

     

     

     

     

     

    Anonymous. The phormula you said works, except for the phormula to calculate 'Cumul Qty'. 

    So, I have changed phormula por Cumul Qty and it works.

     

     

    Cummulative Quantity = 

CALCULATE(SUM(Sales[Quantity]);FILTER(ALL('Calendar'); 'Calendar'[Date] <= MAXX('Calendar';'Calendar'[Date])))

     

     

     

    Diff = 
	[Cummulative Quantity]
	- CALCULATE(
		[Cummulative Quantity];
		FIRSTDATE( 'Calendar'[Date] )
	)

     

     

    And it works.....

     

    Thanks

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Angel ,

       

      Thanks for replying back.

       

      Yes, for my solution to work, Cumulative Quantity has to be a column in the Table. 🙂

       

      Thanks,

      Harsh Nathani

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        This is what your measure should be:

        [Cumul Qty] = // that's faster and simpler
var __lastVisibleDate = MAX( Calendar[Date] )
return
	CALCULATE(
		SUM( Sales[Quantity] ),
		Calendar[Date] <= __lastVisibleDate
	)

        My prev version returned an error because you cannot use the shortened syntax for filters when there's a function involved. The above version is the best you can have.

         

        Best

        D