Forum Discussion

gujralh's avatar
gujralh
Helper I
2 years ago
Solved

How to create the measure for getting the Value difference between 2 dates

Hi All

 

Please help me in creating the measure for getting the value difference between 2 dates i.e Existing date with last 7th date.

 

Here in below table values against each date is also a measure. So I want another measure which gives difference b/w last date (7/9/2024) and last date - 7 ( (7/3/2024) = 0.005 (99.866-99.861).

3rd measure would be their % difference i.e (0.005/last day-7 value)*100

 

 

Thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi gujralh ,

     

    Thank you for your reply.

     

    Please create the following two measures to show the value difference and % value difference for dates every seven days:

     

    Measure_Difference_7Days = 
VAR CurrentDate = MAX('Table'[Date_ID])
VAR PreviousDate = CurrentDate - 6
VAR MeasureCurrent = [Measure1]
VAR MeasurePrevious =
   CALCULATE(
       [Measure1],
       FILTER(
           ALL('Table'),
           'Table'[Date_ID] = PreviousDate
       )
   )
RETURN
   IF(
       ISBLANK(MeasurePrevious),
       BLANK(),
       MeasureCurrent - MeasurePrevious
   )

     

     

    Measure_Difference_Percentage_7Days = 
VAR CurrentDate = VALUE(MAX('Table'[Date_ID]))
VAR PreviousDate = CurrentDate - 6
VAR MeasureCurrent = [Measure1]
VAR MeasurePrevious =
   CALCULATE(
       [Measure1],
       FILTER(
           ALL('Table'),
           VALUE('Table'[Date_ID]) = PreviousDate
       )
   )
VAR MeasureDifference =
   IF(
       ISBLANK(MeasurePrevious),
       BLANK(),
       MeasureCurrent - MeasurePrevious
   )
RETURN
       MeasureDifference / MeasurePrevious

     

     Result for your reference:

    Best regards,

    Joyce

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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gujralh ,

    Depending on your requirements, we can create following measures to meet your needs.

     

    We start by creating a measure called maxDateValue to get the value of the latest date in the table:

     

    maxDateValue = VAR maxDate= MAX('Table'[Date_ID])

VAR maxDateValue=CALCULATE([Measure1],FILTER('Table','Table'[Date_ID]=maxDate))

RETURN maxDateValue

     

    We then create a new measure called maxDateValueBefore7 in the same way to get the value corresponding to the seven days younger than maxDate:

     

    maxDatebefore7Value = VAR maxDate= MAX('Table'[Date_ID])

VAR maxDateValue=CALCULATE([Measure1],FILTER('Table','Table'[Date_ID]=maxDate))

VAR maxDatebefore7=maxDate-6

VAR maxDatebefore7Value=CALCULATE([Measure1],FILTER('Table','Table'[Date_ID]=maxDatebefore7))

RETURN

maxDatebefore7Value

     

    Later, we can use the previous two measures to create the destination measures as shown below:

     

    ValueDifference = [maxDateValue]-[maxDatebefore7Value]

     

     

    %difference = ([ValueDifference]/[maxDatebefore7Value])*100

     

    Result for your reference:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

     

    Best regards,

     

    Joyce

     

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

    • gujralh's avatar
      gujralh
      Helper I
      2 years ago

      Thanks Anonymous , single output wise it giving ok but i need it for all dates and calculate for all upcoming dates.

       

      Against each dates, how it will give the value difference and %value difference with respect to last 7 days.

      With provided sloution , here reflecting same values/infinity  against each date

       

       

       

       

  • gujralh's avatar
    gujralh
    Helper I
    2 years ago

    Anonymous  Do we have any solution to get the output against each date??

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi gujralh ,

       

      Thank you for your reply.

       

      Please create the following two measures to show the value difference and % value difference for dates every seven days:

       

      Measure_Difference_7Days = 
VAR CurrentDate = MAX('Table'[Date_ID])
VAR PreviousDate = CurrentDate - 6
VAR MeasureCurrent = [Measure1]
VAR MeasurePrevious =
   CALCULATE(
       [Measure1],
       FILTER(
           ALL('Table'),
           'Table'[Date_ID] = PreviousDate
       )
   )
RETURN
   IF(
       ISBLANK(MeasurePrevious),
       BLANK(),
       MeasureCurrent - MeasurePrevious
   )

       

       

      Measure_Difference_Percentage_7Days = 
VAR CurrentDate = VALUE(MAX('Table'[Date_ID]))
VAR PreviousDate = CurrentDate - 6
VAR MeasureCurrent = [Measure1]
VAR MeasurePrevious =
   CALCULATE(
       [Measure1],
       FILTER(
           ALL('Table'),
           VALUE('Table'[Date_ID]) = PreviousDate
       )
   )
VAR MeasureDifference =
   IF(
       ISBLANK(MeasurePrevious),
       BLANK(),
       MeasureCurrent - MeasurePrevious
   )
RETURN
       MeasureDifference / MeasurePrevious

       

       Result for your reference:

      Best regards,

      Joyce

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

       

      • gujralh's avatar
        gujralh
        Helper I
        2 years ago

        Thanks much Anonymous, works well for me now!