Forum Discussion

palvarez83's avatar
palvarez83
Helper I
7 years ago
Solved

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...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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's avatar
    AlB
    7 years ago

    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?               

palvarez83's avatar
palvarez83
Helper I
7 years ago

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,

AlB's avatar
AlB
Community Champion
7 years ago

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 :smileyvery-happy: :smileyvery-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's avatar
    AlB
    Community Champion
    7 years ago

    palvarez83

    On your question regarding the semicolons and the commas, I'm not positive but I believe it has to do with your machine's locale settings. If it's US or others that don't use the comma as decimal point, it will be comma for the code. Otherwise it will be "; ". 

  • palvarez83's avatar
    palvarez83
    Helper I
    7 years ago

    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.

    • palvarez83's avatar
      palvarez83
      Helper I
      7 years ago

      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.

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        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.