Forum Discussion

palvarez83's avatar
palvarez83
Icon for Helper I rankHelper 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 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.

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?               

30 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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]
)

     

    • palvarez83's avatar
      palvarez83
      Icon for Helper I rankHelper I
      7 years ago

      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.

       

       

      • palvarez83's avatar
        palvarez83
        Icon for Helper I rankHelper 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,