cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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!

1 ACCEPTED SOLUTION
Community Support

Hi @gujralh ,

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
``````

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 5
Helper I

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

Community Support

Hi @gujralh ,

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
``````

Best regards,

Joyce

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

Helper I

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

Community Support

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``

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.

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors