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

Frequent Visitor

## Calculate the difference in values between previous dates

Hello,

Please can you help me to return the difference between two values within some given dates as below;

So, I would like to substract the previous date value from the current one ie ((Eng FullTime) on the 13/10/2023) - ((Eng FullTime) on the 06/10/2023)

The (Eng Fulltime) is a measure that was calclulated as below;

Eng FullTime = CALCULATE(DISTINCTCOUNT('ENG SOB'[Employee]), FILTER('ENG SOB', 'ENG SOB'[Groups] = "Engineer"))

Many thanks in advance!
1 ACCEPTED SOLUTION
Frequent Visitor

Many thanks for your assistance!

I have now figured it out using the following;

Weekly Difference =
Var _selecteddate = VALUES('Date Friday Only'[Date])
var _maxdate = MAXX(_selecteddate, 'Date Friday Only'[Date])
var _value2 = CALCULATE([Eng FullTime], 'Date Friday Only'[Date] = _maxdate)
var _value3 = CALCULATE([Eng FullTime], 'Date Friday Only'[Date] = _maxdate -7)

Return
_value2 - _value3

_value3 has -7 because the dates occur every 7 days, thus returns the date for the previous week

Thanks
5 REPLIES 5
Super User

@Uka See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hello @Greg_Deckler ,

The solution did not work for me as I got a wired result as below;

Below is the code that I used;

#Result =
VAR __Current = CALCULATE(DISTINCTCOUNT('ENG SOB'[Employee]), FILTER('ENG SOB', 'ENG SOB'[Groups] = "Engineer"))
VAR __PreviousDate = MAXX(FILTER('ENG SOB','ENG SOB'[Operational Date] < EARLIER('ENG SOB'[Operational Date])),[Operational Date])
VAR __Previous = MAXX(FILTER('ENG SOB',[Operational Date]=__PreviousDate),[Eng FullTime])
RETURN
__Current - __PreviousDate

@Greg_Deckler Please can you offer another method?

Super User

@Uka Post your sample data as text/table and I'll take a look.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Many thanks for your assistance!

I have now figured it out using the following;

Weekly Difference =
Var _selecteddate = VALUES('Date Friday Only'[Date])
var _maxdate = MAXX(_selecteddate, 'Date Friday Only'[Date])
var _value2 = CALCULATE([Eng FullTime], 'Date Friday Only'[Date] = _maxdate)
var _value3 = CALCULATE([Eng FullTime], 'Date Friday Only'[Date] = _maxdate -7)

Return
_value2 - _value3

_value3 has -7 because the dates occur every 7 days, thus returns the date for the previous week

Thanks
Super User

@Uka That works!

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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