Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afhlmd
Frequent Visitor

How to obtain the daily values from the accumulated

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.

image.png

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

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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 darek Thank you so much for the solution! It works fine, but I ended up with one problem regarding the method that you used. I think the "IF (HASONEVALUE" is preventing PBI to give me the Totalof the daily movements. Can we work around this?
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Thank you for the reply Darek!

I'm not in my computer right now, but just from reading your proposed solution I believe it will work. As soon as I test it I'll comeback here and give you the feedback but I believe it will do the trick! Thank you so much

Best regards,

Affonso

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors