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 depending on the dates selected by slicers.

 

Data structure:

 

DateQuantityCummulative Quantity
01/02/20201515
02/02/2020722
03/02/2020224

 

 

I have created a measure to get the cummulative value:

 

 

Cummulative Quantity = 

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

 

 

The goal is showing the cummulative difference in a card depending on a date slicer. Examples:

Date slicer valuesDifference
From 01/02/2020 to 02/02/20207
From 01/02/2020 to 03/02/20209
From 02/02/2020 to 03/02/20202

 

Can anyone help me, please?..

 

Thanks in advance,

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

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Angel ,

     

    1. Create a Calculated Column (this is to get the Cumulative Totals of the Previous Date)

     

    Demoted CQ =
    VAR maxi =
    CALCULATE (
    MAX ( Data1[Cummulative Quantity] ),
    FILTER ( ALL ( Data1 ), Data1[Date] < EARLIER ( Data1[Date] ) )
    )
    VAR mini =
    CALCULATE (
    MIN ( Data1[Cummulative Quantity] ),
    FILTER ( ALL ( Data1 ), Data1[Date] = MIN ( Data1[Date] ) )
    )
    RETURN
    IF ( Data1[Date] = MIN ( Data1[Date] ), mini, maxi )

     

     

    2. Create a Calculated Column (This Calculates the Difference between the Cumulative Totals)

     

    Difference = Data1[Cummulative Quantity] -Data1[Demoted CQ]
     
    3. Drag your Slicer and Card . Slicer will have the Dates and Card the Difference.
     
     
    Regards,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! and give Kudos
  • 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