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

[gujralh]

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!

[gujralh]

@v-yajiewan-msft  Do we have any solution to get the output against each date??

[v-yajiewan-msft · Accepted solution]

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]

Thanks much @v-yajiewan-msft, works well for me now!

[v-yajiewan-msft]

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]

Thanks @v-yajiewan-msft , 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