Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Looking for some help if possible.
I am trying to write some DAX in DAX studio to return the previous cancelled date for an account for a broker (however not getting very far).
What I am trying to achieve is the below - where I can return the prev cancelled date (Column H) from the data stored in column B for the same broker.
I am currently writing DAX against a shared dataset which holds a lot of data as we snapshot all our accounts everyday.
This is where I have gotten to so far with my DAX as I have seen online I needed to use the EARLIER function - but I just cannot get the "Prev Cancelled Date" column to work. I assume it's do with my FILTER function inside that calculated column but to be honest its just confusing me.
EVALUATE
CALCULATETABLE (
SUMMARIZE (
FactCase,
DimCase[Account Number],
FactCase[Cancelled Date],
DimBroker[Broker First Name],
DimBroker[Broker Last Name],
DimBroker[Broker Email],
FactCase[Snapshot Date],
"Row Count", [Row Count],
"Prev Cancelled Date",
CALCULATE (
MAX ( FactCase[Cancelled Date] ),
FILTER (
FactCase,
DimBroker[Broker Email] = EARLIER ( DimBroker[Broker Email] )
&& DimBroker[Broker First Name] = EARLIER ( DimBroker[Broker First Name] )
&& DimBroker[Broker Last Name] = EARLIER ( DimBroker[Broker Last Name] )
&& FactCase[Cancelled Date] < EARLIER ( FactCase[Cancelled Date] )
)
)
),
FactCase[Snapshot Date] = DATEVALUE ( "2023-07-12" ),
FactCase[Cancelled Date] <> BLANK ()
)
ORDER BY DimBroker[Broker Email], FactCase[Cancelled Date] DESC
As you can see below from using the DAX I have provided for the "Prev Cancelled date" column, I am returning blank data for that whole column.
I do have some sample data if required but it won't let me upload to this message.
Any help that could be given would be greatly appreciated
Thanks
Solved! Go to Solution.
@LukeB 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
@LukeB 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
Hello @Greg_Deckler @LukeB,
My 1st column has a list of Equipment, and the 2nd column has the date/time they failed in the date/time format. For each of these equipment, I am trying to find the difference between date/time they failed (difference between rows) and calculate their summation at the end.
I created the Index column and wrote the following DAX code to solve this issue:
DIFF =
var _lasttime = CALCULATE(MAX('Sampling'[BreakDownDate]),FILTER('Sampling','Sampling'[Index]=EARLIER('Sampling'[Index])-1))
return
DATEDIFF(_lasttime,'Sampling'[BreakDownDate],MINUTE)
It is giving me the right number if I just select(filter) just 1 equipment but as I keep adding Equipments to the table it gives me wrong summation of Breakdown date towards the end. As a whole on 2000+ different equipments, my numbers are not that far. But individually for those equipments my numbers are sharply and significantly different for many equipments.
Can you please suggest a better DAX code than above to resolve this issue further?
@Greg_Deckler Actually I got it working with the below from your article. I removed the "Prev Decline" column from inside the summarize and then added wrapped it in a ADDCOLUMNS further down which did the trick.
Thanks a lot for the help!
@Greg_Deckler Thanks for coming back so quick.
So looking at the article you sent it adds a calculated column into the physical dataset as per the below screenshot?
Is there a way to do it just where I can create that column inside my below DAX query? As what I am looking for is quite bespoke to a single report and don't want it going into the general dataset.
Apologies if I have missed the point here - DAX confuses the life out of me!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |