Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a database which contains numbers already accumulated and I want to create a measure that subtracts the accumulated from one day to its previous in order to obtain the daily movement. Can I create a measure in DAX to do this? Here is an example of what I need.
I´ve made it easily in an Excel Pivot Table but I´m not managing to do it em PBI. Can you help me?
Thank you
Solved! Go to Solution.
Daily differences (or any period's differences) can be obtained for any measure pretty easily. Say your measure is imaginatively called [Measure]. The difference from the previous period can be obtained via:
[Measure Diff from Prev Day] = var __thisPeriodMeasure = [Measure] var __prevPeriodMeasure = CALCULATE( [Measure], PREVIOUSDAY( 'Calendar'[Date] ) ) var __isOneDayVisible = HASONEVALUE( 'Calendar'[Date] ) return if( __isOneDayVisible, __thisPeriodMeasure - __prevPeriodMeasure)
Of course, for this to work you have to have a Calendar table marked as a date table, where [Date] is the key column that joins to the underlying fact table on a date field.
Best
Darek
Daily differences (or any period's differences) can be obtained for any measure pretty easily. Say your measure is imaginatively called [Measure]. The difference from the previous period can be obtained via:
[Measure Diff from Prev Day] = var __thisPeriodMeasure = [Measure] var __prevPeriodMeasure = CALCULATE( [Measure], PREVIOUSDAY( 'Calendar'[Date] ) ) var __isOneDayVisible = HASONEVALUE( 'Calendar'[Date] ) return if( __isOneDayVisible, __thisPeriodMeasure - __prevPeriodMeasure)
Of course, for this to work you have to have a Calendar table marked as a date table, where [Date] is the key column that joins to the underlying fact table on a date field.
Best
Darek
Hi there.
The line
var __isOneDayVisible = HASONEVALUE( 'Calendar'[Date] )
is there to make sure that the calculation of the difference happens only when one day is visible in the current context. If you want to allow any time chunks, for instance, months, then you should revise the logic because PREVIOUSDAY(...) returns one date only even if many dates are visible - it returns the first date visible in the context and shifts it 1 day back. So, this whole calculation of differences makes sense for one day but you have to decide what you want to do when more days are visible.
Best
Darek
@Anonymous
No worries about the HASONEVALUE issue I´ll figure out something to work around that.
Now I have to manage another backfire of the solution. As you warned me the PREVIOUSDAY( ) function shifts only one day (as expected) but the problem is that my dates are not linear because the financial movements happen only in weekdays. is there a way to solve this issue?
I´ve read the link you sent me and I´ll try to upload a csv with a sample file to make things more clear for the people participating in the discussion (I just don´t know how to do it yet!)
Thanx for all the patience ans attention.
Affonso
Sure, there is a way. There's always a way 🙂
Instead of using PREVIOUSDAY(...) you'd have to calculate the correct day from the current context. There are many ways to do it. The day you need to retrieve depends on your requirements, obviously.
Your 'Calendar' should have a column that would mark each day as 'Weekday' or 'Weekend'. If you do have it, then the previous day you want is calculated as (it's one of the options):
The last day in 'Calendar' that is marked as 'Weekday' and is before the current day.
The measure to retrieve this day would be:
[Last Workday] := var __currentDay = SELECTEDVALUE( 'Calendar'[Date] )
var __lastWorkday =
CALCULATE(
MAX( 'Calendar'[Date] ),
'Calendar'[Date] < __currentDay,
'Calendar'[Weekday/Weekend] = "Weekday",
ALL( 'Calendar' )
)
RETURN
__lastWorkday
You just should replace the PREVIOUSDAY(...) function with this measure.
Best
Darek
Hi there Darek,
Just to give you feedback that I´ve just implemented the last solution that you proposed and it works perfectly. Thank you so much.
Best,
Affonso
I have the same issue, except that I am looking at tank levels. I am needing the pounds used per day, but they could receive a truck shipment at anytime during the day and would cause the pounds to increase. Any recommendations on handling that?
Thanks,
Spartacus499
Hi there.
I'd suggest you either look at the above solution and make slight changes to it or... if it's not doing the job, please ask the question properly following the rules outlined in here:
How to Get Your Questions Answered Quickly
Currently, I can't give you any clues as the question is too foggy. Sorry.
Best
Darek
I´m sorry, I´ll read the material and try to be more clear about the problem. It´s the first time I post a doubt in the forum so please don´t give me a hard time, in time I´ll understand how things work in the forum.
Best Regards,
Affonso
No worries, mate. Nobody's trying to give you a hard time 🙂 Trust me. It's in YOUR BEST INTEREST to be as clear as the sun so that others can give you solutions quickly. Just please remember that you DO understand the problem because you've been working on it for some time. Others are new to it and therefore you have to be absolutely clear about what it is that you need and the model you're working with 🙂
Best
Darek
@afhlmd share the sample of raw data to get you the solution. what you shared is already pivoted data, Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |