DAX Buffer Calculation Query

DAX DAX Calculations
1

Hi There,

I’m looking for some assistance with writing a DAX measure.

I have 2 Tables (Dates & Activity)

Current the Activity table has to 2 x Date fields (Work Recvd & Work Complted) with the dates table connected to Work Recvd.

I am trying to establish what the Buffer level of activity is with following concepts:

Net Flow = Inflow- Outflow
Buffer =Net Flow + previous day’s buffer (+ or minus)

I’m not sure how i can write the DAX formula. when i use the date add function i get incorrect results. Any assistance or please point me in the direction of any previous posts/tutorials with similar concepts.

I have a screenshot to attach but for some unknown reason it will not upload the screenshot.

Thanks

2

Hi, Adsa! Can you share your file? It would help a lot.

3

@Ivanka

I’m not able to attach the files here.

You can access the file here instead:

Link

4

@adsa,

I think you’ll find this recent thread very helpful:

Today vs Yesterday - #2 by BrianJ

  • Brian
5

@BrianJ

Thanks for the info. I’ve given it a shot but still can’t get to work just yet.

This is what I have so far:

Inflow CLink = 
SUM ( CSDepartmentActivity[Clink] )


Outflow CLink = 
 CALCULATE (
    SUM ( CSDepartmentActivity[Clink] ),
    FILTER (
        VALUES ( CSDepartmentActivity[CompleteDate] ),
        CSDepartmentActivity[CompleteDate]<=MAX ( Dates[Date] )
    ),
    FILTER (
        VALUES ( CSDepartmentActivity[CompleteDate] ),
        CSDepartmentActivity[CompleteDate] >= MIN ( Dates[Date] )
    )
)


Net Clnk =
[Inflow CLink] - [Outflow CLink]

Buffer =
VAR a = [Net Clnk]
VAR b =
    CALCULATE ( [Net Clnk], DATEADD ( Dates[WkEnd], -7, DAY ) )
RETURN
    a + b

Buffer still does not calculate correctly (See screenshot)

image
image.png591×527 18.8 KB

Thanks

6

@adsa,

I’m probably missing something obvious here, but to me it looks like buffer is calculating exactly as expected given the content of the measure (see screenshot below - in each line, a+b = Returned value, i.e., value on a given date plus value 7 days earlier = return)

image

What result were you looking to get?

  • Brian
7

@BrianJ

This is what i should be getting:

image
image.png559×505 7.13 KB

8

@adsa,

OK, so Buffer for any given date should just represent the cumulative total of [Net Clnk] to that date. Because you’re filtering by the fiscal year slicer, you’ll also want to incorporate ALLSELECTED into your cumulative total pattern. This short video will walk you through exactly how to craft that measure:

Hope this is helpful. Give a shout if you have any problems.

  • Brian
9

@BrianJ

Thanks for that tutorial. I’ve tried to use that logic in my cumulative total but still having issues :

Buffer =
CALCULATE (
    [Net Clnk],
    FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

image
image.png579×626 23 KB

10

@adsa,

Very close. What’s missing is the aggregator that creates the total. If Net Clnk were a column, you would use SUM, but since it’s a measure you’ll need to restructure the cumulative total formula a little to use SUMX.

Buffer =

CALCULATE (
    SUMX (
        FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) ),
        [Net Clnk]
    )
)

This should work for you, but if it doesn’t, please post your PBIX file so I can diagnose the issue. Also, make sure that Dates is marked as a date table, which is necessary to ensure that time intelligence functions, including cumulative totals, work correctly.

  • Brian
11

@BrianJ

Thank you for that. That actually works.

I do have one small issue though, when you look at all the dates (Unfiltered from the slicer) it shows up correctly. However when you add the date from the slicer (eg- FY 20) the carry from the previous FY year does not come through.

See screenshots below:

All:

image
image.png581×591 50.9 KB

Filtered:

image
image.png363×591 20.2 KB

12

@adsa,

This is a very interesting issue. You would not expect DAX to have any “awareness” of prior fiscal years’ balance when a slicer is applied, so we will have to create that with custom code. If you could please post your PBIX file today, I can work up a solution tonight.

  • Brian
13

@BrianJ Thank you for your response.

I have a feeling it may have something to do with the relationship between the dates table.

Info.xlsx (331.8 KB) Department Activity.pbix (3.0 MB)

Attached as requested.

Thank You

14

@adsa,

This turned out to be a really interesting problem. I’ve got a solution worked out, but have one final bug in the visual to squash. Need to call it a night, but will get you the full solution tomorrow.

  • Brian
15

@BrianJ

Thanks. Keen to know how you went about resolving the issue.

16

@adsa,

FYI - I started a new thread on this one.

Getting Measure to Display as Constant in Context

Got 95% of the way there and then got absolutely stuck on the final step. I’m extremely stubborn, so I hammered away at it til far too late into the night, when I finally conceded and called for help. You can see the status of the current solution, both screenshot and PBIX at the link above. I’m confident that with the help of the forum, we’ll crack the final issue and complete the solution.

  • Brian
17

@BrianJ

Tthank you for looking into this thread and with your assistance. Hopefully we have a solution soon .

18

Hi @adsa, a new thread has been created to assist you in creating a solution to your issue. We are now tagging this topic solved, please visit the [link] to see the new solution provided by @BrianJ. Thanks! (Getting Measure to Display as Constant in Context)