Lookup DateKey from another column's running total?

[palvarez83]

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.

[AlB · Accepted solution]

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

[palvarez83]

@AlB,  Thanks again.  I wasn't aware that expanding the measure code had a different behavior than invoking the measure.  So that is a good to know thing as well.

[AlB]

@palvarez83

and one thing that we haven't commented on but caught my attention from the beginning: why do you have the 'Productivity' column in your date table, dCalendar?

[palvarez83]

@AlB,

Good question.  I am working on a large project in Asia that is made up of smaller projects (modules) where we have to do some commissisoning one each module after it is built.  We get a forecasted or actual start and end date to do commissioning on each module as a given.  Each module has a planned commissioning duration which right now usual exceeds the given window that we are given.  So I need to answer a few questions.

1.  In that given window how much uncomplete work would I have left over for each module?  I compute this as the

( [NeededWorkWorkdays]- [WorkingDaysGiven] ) / [NeededWorkWorkDays] * [Man-hours to be worked]...

2.  If there is going to be work left over, by what date in the future could we complewte it?  That/s where you helped me sum working days using the ADDCOLUMNS function.  I pull the calendar table fillter based on dates after the end date I'm given and using a running total I figure out how many working days I need to accumulate and return that date.

That was the summing of workdays part.  The zeros represent sundays and public holidays.  Next came the productivity factors summation. 

Since we are trying to complete this left over work as quickly as possible, our team has been working overtime 1.4 producitfy factor instead of just 1.0 which represent the assumed working hours baseline.  Then starting certain dates, we are forecasting a partial night shfit to start and give the dayshift guys a break from overtime work ( 1.6 productivyt factor) and then a full night**bleep** at a later date which we assume will only be 80% as productiy as the day shift so you would have 1.8 as that day's productivy.   So the dates which these changes in producity happen apply across all modules.  So I imported them from an excel table, merged these factors onto the calendar table using power query by matching dates and filled downwards.  Then any remaining null values got replaced with 1.0 (default)

By summing  the daily producity factors  from the calendar table filtered for working days only, I can answer the same questions as above, for the nightshift scenario:  How much work will be leftover and if given an extension, up until which date I would need it.  This would help us forecast when we actually need nightshift to complete the work. 

Since the productiy factor is an assumption that depends on date only and is to be referenced in 4 different calculations for each module, it made sense to have it in the Calendar table.   One possible improvement I might do next is adjust producity factors based on how many simulaneous modules are available to work on on a given date, but this is good enought for now.  Perhaps there would have been a more efficent way of doing this, but I'm reactiving to what I have and it has been a fun experience for me to start learning DAX, powerquery, and Power Bi. 

Thank you for all your help!

[AlB]

@palvarez83

Alright. Interesting challenge.

So how about some kudos maybe ?

[palvarez83]

@AlB,  Done.  Kudos given.  🙂

[AlB]

@palvarez83

Wow, that was a bit over the top. Thanks

[AlB]

@palvarez83

If you don't know the Italian gurus yet, they have a wealth of very interesting stuff here.

Their articles/blogs and books on DAX are really good. 

[AlB]

@palvarez83

I was wondering, are the filtering conditions

dCalendar[Date] > DATE ( 2019, 1, 11 )
                    && dCalendar[Workday] = 1

 fixed? If they are not and you're interested in seeing the results for other scenarios I think it would be better to do the filtering through slicers. Then you can change the conditions dynamically.    

[palvarez83]

@AlB

dCalendar[Workday] = 1 is a fixed condition.

dCalendar[Date] > DATE ( 2019, 1, 11 ) wiill be replaced by a measure dCalendar[Date] > [DateNeeded].  That measure does not seem to be affected by context transition in what I am trying to do now.

[AlB]

@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 "; ".