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
palvarez83
Helper I
Helper I

Lookup DateKey from another column's running total?

Hello,

Is there a way to use DAX filter/calculate function combination to return the value froma row from a certain using a running total/cumulative from a different column? I have a table dCaldenar like the following:

 

DateKey               Workday              Productivy

10/1/18                1                              1.4

10/2/18                0                              1.4

…                             …                             …

12/31/19              1                              1.8

 

 

I would like to pass in a date measure to filter that table as follows:

 

Filter(dCalendar, dCalendar[DateKey]>= [DateMeasure] && dCalendar[Workday]=1)

 

 

Then I would like to somehow start summing the dCalender[Productivity] column from the filtered table, like a running total, and return the DateKey for the earlierst row where that running total is greater than or equal to some known value from another measure called [ProductivityNeeded].

 

  1. Is there a way to do that starting summing productivity from the top row downwards?
  2. What if I needed to reverse it? That is start summing from the bottom upwards?

 

Any other suggestions?  Please help.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

@palvarez83

 

Try adding an ALL() to the base table for the filter as below. Seems to work. Check out if it is so and then we can discuss what was at play.

 

Working Days Given :=
CALCULATE (
    SUM ( dCalendar[Workday] ),
    FILTER (
        ALL(dCalendar),
        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
            && dCalendar[Workday] = 1
    )
)
    - SUM ( fCommTime[Days Lost] )

 

View solution in original post

@palvarez83

No worries. glad it helped. 

Your code for the measure:

 

Working Days Given :=
CALCULATE (
    SUM ( dCalendar[Workday] ),
    FILTER (
        dCalendar,
        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
            && dCalendar[Workday] = 1
    )
)
    - SUM ( fCommTime[Days Lost] )

 

When we invoke this measure within the other piece of code, we have a row context from the ADDCOLUMNS. As discussed earlier, I was afraid the context transition would play unwanted tricks. When I initially saw your code, though, it seemed fine because you are using the whole dCalendar table as base table for your filtering operation. That should be enough to override the effects of context transition. BUT, and here comes the interesting part, every time a measure is invoked, the engine wraps the measure in a CALCULATE. You probably are aware of that. So what we effectively have when we call your measure is:

 

CALCULATE (
    CALCULATE (
        SUM ( dCalendar[Workday] ),
        FILTER (
            dCalendar,
            dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
                && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
                && dCalendar[Workday] = 1
        )
    )
        - SUM ( fCommTime[Days Lost] )
)

   

The outermost CALCULATE does not have filter arguments and the filter resulting from context transition is applied fully. That filter is the current row of the table (the ADDCOLUMNS table), as you know. Then when the engine executes the inner CALCULATE we have that row as filter and that is applied directly to dCalendar in 

FILTER(dCalendar;....)

The base table for the filter operation is just that one row instead of the full table that we would want. That is why you need the ALL( ).

Does that help?               

View solution in original post

30 REPLIES 30
AlB
Super User
Super User

Hi @palvarez83

 

How about the following. I have not tested as I don't have your model but it gives you the general idea.

The innermost FILTER is what you provide. Then we add a column calculating the running total and lastly filter [ProductivitNeeded]. For the other version (bottom to top)  you just change the <= operator in
dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )  
   for >=, i.e.
dCalendar[DateKey] >= EARLIER ( dCalendar[DateKey] )

 

Be careful with the measures [DateMeasure] and [ProductivityNeeded] as they are being invoked in row context and will trigger context transition. You might have to expand the code to avoid the implicit CALCULATE.

 

FILTER (
    ADDCOLUMNS (
        FILTER (
            dCalendar;
            dCalendar[DateKey] >= [DateMeasure]
                && dCalendar[Workday] = 1
        );
        "RunningTotal"; CALCULATE (
            SUM ( dCalendar[Productivity] );
            ALL ( dCalendar );
            dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
        )
    );
    [RunningTotal] >= [ProductivityNeeded]
)

 

@AlB,

 

Thank you.  This looks promising.  I'm working on it.  A couple of questions.

 

1.  It seems like the formula you suggest would return a table.  Is that correct?  Would I then need to enclosure the min() function to return the earliest dCaldendar[DateKey] from that column of the filtered table with added column?

 

2.  Dumb question:  is there a difference when writting DAX with semicolons like in the code you suggested vs. commas?  I had not seen the semicolons before.

 

 

@AlB,

Yes, it appears it returns a table....   I test it and the running totals seem to work fine.

 

I am stuck on how to return the [DateKey]  column from the table created by the code you suggested, so I can then apply a Min() or Earliest() function to return the first/ earliest/minimum datekeyvalue.

 

Any suggestions?

 

Thank you,

Hi @palvarez83

Yeah it's a table.

I'm just realizing that I misread your question. Well, I actually think I read it correctly but then somehow I forgot a part of it when I was putting the code together and left it incomplete. Sorry about that.

Try the following for your first scenario. I've only added the SELECTCOLUMNS and FIRSTNONBLANK. I am going through an existential crisis Smiley Very Happy Smiley Very Happy with FIRSTNONBLANK, I yesterday discovered that it works in a different way that I thought, but I believe it will work in this case.

The other scenarios should be a combination of FIRST-LASTNONBLANK and the <= , >=  in the condition as explained in the initial post.

Let me know if it works

 

DateKeyTopBottom =
FIRSTNONBLANK (
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    dCalendar;
                    dCalendar[DateKey] >= [DateMeasure]
                        && dCalendar[Workday] = 1
                );
                "RunningTotal"; CALCULATE (
                    SUM ( dCalendar[Productivity] );
                    ALL ( dCalendar );
                    dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
                )
            );
            [RunningTotal] >= [ProductivityNeeded]
        );
        "DateKey2"; [DateKey]
    );
    1
)

 

@AlB,

Thank you so much for the help so far.  Looks like we're making some headway.  I am using a table created from the DAX formula help troubleshoot each step by seeing the intermediate tables generated. 

 

TroubleshootProductivityRunningTotal =
ADDCOLUMNS (
    FILTER (
        dCalendar,
        dCalendar[Date] >= DATE ( 2019, 1, 11 )
            && dCalendar[Workday] = 1
    ),
    "RunningTotal", CALCULATE (
        SUM ( dCalendar[Productivity] ),
        ALL ( dCalendar ),
        dCalendar[Date] <= EARLIER ( dCalendar[Date] )
    )
)

 I think I found what my issue is.  When I use the ADDCOLUMNS function, the CALCULATE expression is computing the [RunningTotal] for each row, based on the original, unfiltered dCalendar table, instead of computing it based on the filtered table created by the FILTER function. 

 

 

Is there a easy way to correct this?   If I remove ALL(dCalendar), it returns the same (incorrect) running total for each row.

 

Thank you.

@AlB

 

I played around by moving the location of the filters and I get the correct intermediate table as follows:

 

TroubleshootProductivityRunningTotal =
FILTER (
    ADDCOLUMNS (
        dCalendar,
        "RunningTotal", CALCULATE (
            SUM ( dCalendar[Productivity] ),
            FILTER (
                ALL ( dCalendar ),
                dCalendar[Date] > DATE ( 2019, 1, 11 )
                    && dCalendar[Workday] = 1
                    && dCalendar[Date] <= EARLIER ( dCalendar[Date] )
            )
        )
    ),
    [RunningTotal] >= 30.6
)

Even when I enclose that with the SELECTCOLUMNS function and then the FIRSTNONBLANK() function, I get the correct answer...

 

However, I run into trouble  when I  need to change the [RunningTotal] >= 30.6 to a use a measure [RunningTotal]>= [RunningTotalNeeded].   It appears to be evaluating the [RunningTotalNeeded] in the wrong context and I am not sure how to correct it.  I suspect it has to do something with context transition as you mentioned before.  Thank you for your ongoing support.

Hi @palvarez83

Setting all filtering conditions together as you've done is a smart a quick solution. The problem with doing the filtering (in red) in the first parameter of the ADDCOLUMNS, as we had done initially, is that come the CALCULATE  we have no direct way to refer to that filtered version, since dCalendar is interpreted by the engine as the full table.

   

 

ADDCOLUMNS (
        FILTER (
            dCalendar;
            dCalendar[DateKey] >= [DateMeasure]
                && dCalendar[Workday] = 1
        );
        "RunningTotal"; CALCULATE (
            SUM ( dCalendar[Productivity] );
            ALL ( dCalendar );
            dCalendar[DateKey] <= EARLIER ( dCalendar[DateKey] )
        )

 Yeah, most likely it is something related to context transition with that measure. Can you show the code for [RunningTotalNeeded]? Then I'd be able to do more.

Maybe just expanding the code for the measure would suffice.     

 

@AlB

 

There are 2 versions of this that I am trying to run... the one I am referencing above is summing dCalendar[Productivity].  The simpler version is very similar, but it is summing dCalendar[Workday].  Here is the code for that one:

 

DateNeed :=
FIRSTNONBLANK (
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                dCalendar,
                "RunningTotal", CALCULATE (
                    SUM ( dCalendar[Workday] ),
                    FILTER (
                        ALL ( dCalendar ),
                        dCalendar[Date] > [PD Date]
                            && dCalendar[Workday] = 1
                            && dCalendar[Date] <= EARLIER ( dCalendar[Date] )
                    )
                )
            ),
            [RunningTotal] >= [RunningTotalNeeded]
        ),
        "DateKey", dCalendar[Date]
    ),
    1
)

The [PD Date] measure is not problematic right now, but the [ RunningTotalNeeded] measure is.

 

 

The code for the problematic meaure is:

RunningTotalNeed :=
MAX ( [Baseline HVAC Work Days] - [Working Days Given], 0 )

 

Where those two mearures are calculated as follows:

Baseline HVAC Work Days:=sum ( fCommTime[Planned Work Days] )

 

and

 

Working Days Given :=
CALCULATE (
    SUM ( dCalendar[Workday] ),
    FILTER (
        dCalendar,
        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
            && dCalendar[Workday] = 1
    )
)
    - SUM ( fCommTime[Days Lost] )

@palvarez83

Relationships between fCommTime and dCalendar? Are those two the only tables in your model?

Would it be possible to share the pbix?

 

@AlB

,

No relationship between fCommTime and dCalendar.  There is one more table, dModule that is used for slicers and it is related to fCommTime.

 

Orignially, I was creating this in PowerPivot, but I imported it to PowerBi and recreated the table that I am having issues with.  Link is below.  Sorry for he lack of a proper measure table (don't know how to do that in PowerPivot).

 

https://app.powerbi.com/groups/me/reports/400d02b5-1849-42cd-8d3f-770f77d779ba?ctid=fd799da1-bfc1-42...

 

The visualization has a table I used to replecate the pivot table that was on power pivot.  The last column [Ext. calculation] is the one we are trouble shooting.  This one is missing an if statment to return an alternative answer when the [VAR wo N/S] measure is equal to zero. 

 

The second to last column is the problematic measure that is having trouble filtering [VAR wo N/S] .   It should be evaluating like it is on the table column, but in the [Ext. Calulation] . 

 

The 3rd to last column is the correct answer (when the problematic measure is not equal zero).  So this one should match the last column if the 2nd to last column does not equal zero.  I will fix it with an if stament later.

 

Thank you for your help.

 

@palvarez83

Can't access the link you just posted. Maybe you can share the pbix itself? Possibly with dummy data if there are confidentiality issues 

 

No confidentiality issues.  Since the link didn't work, is there a way to send an attachment? Or how else can I send the PBIX file?  

Is there an email I can send it to or how are these normally shared?

 

Thank you.

@palvarez83

you can just share here the URL to the file from platforms like Dropbox, OneDrive etc. or just upload it to a site like this (no sign in required) and post the URL  here  

@AlB

 

Thank you for that.    Here is the file dropper link:  https://www.filedropper.com/pcow-powerbi-version

 

@palvarez83

 

Try adding an ALL() to the base table for the filter as below. Seems to work. Check out if it is so and then we can discuss what was at play.

 

Working Days Given :=
CALCULATE (
    SUM ( dCalendar[Workday] ),
    FILTER (
        ALL(dCalendar),
        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
            && dCalendar[Workday] = 1
    )
)
    - SUM ( fCommTime[Days Lost] )

 

@AlB

 

Wow!  That work.  I also went back through the other version and added the ALL() and it too worked.  Can you explain what is at play there?

 

Thank you so much!!

@palvarez83

No worries. glad it helped. 

Your code for the measure:

 

Working Days Given :=
CALCULATE (
    SUM ( dCalendar[Workday] ),
    FILTER (
        dCalendar,
        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
            && dCalendar[Workday] = 1
    )
)
    - SUM ( fCommTime[Days Lost] )

 

When we invoke this measure within the other piece of code, we have a row context from the ADDCOLUMNS. As discussed earlier, I was afraid the context transition would play unwanted tricks. When I initially saw your code, though, it seemed fine because you are using the whole dCalendar table as base table for your filtering operation. That should be enough to override the effects of context transition. BUT, and here comes the interesting part, every time a measure is invoked, the engine wraps the measure in a CALCULATE. You probably are aware of that. So what we effectively have when we call your measure is:

 

CALCULATE (
    CALCULATE (
        SUM ( dCalendar[Workday] ),
        FILTER (
            dCalendar,
            dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
                && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
                && dCalendar[Workday] = 1
        )
    )
        - SUM ( fCommTime[Days Lost] )
)

   

The outermost CALCULATE does not have filter arguments and the filter resulting from context transition is applied fully. That filter is the current row of the table (the ADDCOLUMNS table), as you know. Then when the engine executes the inner CALCULATE we have that row as filter and that is applied directly to dCalendar in 

FILTER(dCalendar;....)

The base table for the filter operation is just that one row instead of the full table that we would want. That is why you need the ALL( ).

Does that help?               

@AlB , Yes.  This helps a lot.  I had heard of the implicite calculate, but could never figure out what that meant, even with a google search.  Thank you for taking the time to explain this to me. 

@palvarez83

Another option, albeit probably less aesthetically appealing, would be to expand the code for the measure and use it directly instead of invoking the measure. This would eliminate the implicit CALCULATE, thus rendering the ALL() unnecessary:

 

DateNeed :=
FIRSTNONBLANK (
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                dCalendar,
                "RunningTotal", CALCULATE (
                    SUM ( dCalendar[Workday] ),
                    FILTER (
                        ALL ( dCalendar ),
                        dCalendar[Date] > [PD Date]
                            && dCalendar[Workday] = 1
                            && dCalendar[Date] <= EARLIER ( dCalendar[Date] )
                    )
                )
            ),
            [RunningTotal]
                >= CALCULATE (
                    SUM ( dCalendar[Workday] ),
                    FILTER (
                        dCalendar,
                        dCalendar[Date] >= MIN ( fCommTime[ActualStart] )
                            && dCalendar[Date] <= MIN ( fCommTime[ActualEnd] )
                            && dCalendar[Workday] = 1
                    )
                )
                    - SUM ( fCommTime[Days Lost] )
        ),
        "DateKey", dCalendar[Date]
    ),
    1
)

 

 

 

 

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.