Different results for measure vs calculated column in PowerPivot

[jeffreyweir]

Howdy folks. I'm using PowerPivot in Excel, and am struggling to work out why a result I get using calculated columns differs from the result I get if I use Measures to achieve the same thing.

I have a report with a calculation in it that works out the maximum number of days in a row that someone works during the reporting period, using an incrementing counter that already exists in the raw data. That incrementing counter gets reset to 0 whenever the person has a day off, so all I need to do is find the largest value of that counter during a workday, and I have my desired "Longest Stretch" result.

My report covers the last 28 days that someone worked, as well as their planned schedule 28 days into the future. But I only want to return the "Longest Stretch" calculation from runs that started within this period, and I want to ignore any runs that they were in the middle of at the start of the reporting period. Here's an image from the report that should help explain this (if it uploads properly...I'm having issues with this forum)

The lower bound of that period of interest is the first day they have off in the last 28 days. To calculate that lower date, I use the followming calculated column, which works just fine:

FirstDayOff= 
IF([Watchlist],
        CALCULATE(
            MIN(WFTM_Fatigue[SHIFT_DATE]),
            FILTER(WFTM_Fatigue,WFTM_Fatigue[Workday]="OD"),
            FILTER(WFTM_Fatigue,WFTM_Fatigue[EmployeeId]=EARLIER(WFTM_Fatigue[EmployeeId])),
           FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]>=[Historic Start]),
           FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]<[Forecast Start]),
           FILTER(WFTM_Fatigue,[SameDate_Order]=1))
    ,[Forecast Start])

...and then I use that within my code to find the longest stretch they have worked:

LongestStretch =
IF([Watchlist],
    CALCULATE(
        MAX(WFTM_Fatigue[Wseq_Day]),
        FILTER(WFTM_Fatigue,WFTM_Fatigue[Workday]="WD"),
        FILTER(WFTM_Fatigue,WFTM_Fatigue[EmployeeId]=EARLIER(WFTM_Fatigue[EmployeeId])),
        FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]>[FirstDayOff]),
        FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]<[Forecast End]),
        FILTER(WFTM_Fatigue,[SameDate_Order]=1)),
0)

This works fine. But I really wanted to do it as a measure, so I changed the code appropriately. But while my FirstDayOff measure returns the correct date:

FirstDayOff =
CALCULATE(
    MIN(WFTM_Fatigue[SHIFT_DATE]),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[Workday]="OD"),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]>=[Historic Start]),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]<[Forecast Start]),
    FILTER(WFTM_Fatigue,[SameDate_Order]=1))

...that date doesn't seem to be 'respected' by the LongestStretch measure:

LongestStretch= 
CALCULATE(
    MAX(WFTM_Fatigue[Wseq_Day]),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[Workday]="WD"),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]>[FirstDayOff]),
    FILTER(WFTM_Fatigue,WFTM_Fatigue[SHIFT_DATE]<[Forecast End]),
    FILTER(WFTM_Fatigue,[SameDate_Order]=1))

...because some of the results from that LongestStretch field come from before the FirstDayOff result.

I'll post a picture shortly, if I'm able.

Is there somethnig I'm not grasping about Measures here? Is there any reason why my results would differ in some cases? i.e. the calculated column approach correctly ignores any work runs in play when the reporting period started, but the measure approach seems to include them?

[v-huizhn-msft]

Hi @jeffreyweir,

In your calculated column formula, [Watchlist] is a measue or column name? Could you please post some more details? Based on your description, it's hard to reprodcue, is it possible to share your .pbix file and sample data for further analysis?

Best Regards,
Angelia

[Phil_Seamark]

Hi @jeffreyweir,

One reason why a calcuated column will produce a different result to a calculated measure, even if you think they are doing the same thing is the filter context.   Calcualted columns will be calcualted, pretty much when the data is loaded and they will be all but locked in as solid values (apart from a few cases).  This means as you change filters they will mostly remain the same.

Whereas caluclated measures typically calculate on the fly and will will more likely be affected by slicer/filter selections.

In the scenario of a running total of data over a 3 year period.  This can be done both as a column and as a measure.  With no filters, they can produce the same result.  When you apply a date-range filter within the dataset, the measure may re-cacluate from 0 for the data in the date range, whereas the value provided by the calculated column will be like applying a window.

Does that kind of make sense?

[jeffreyweir]

Hi Phil. Yeah, I follow that. I'm just not sure what in my filter setup is causing the difference. I'll have a crack tomorrow at making up a simplified sample file that I can upload, that might help point out exactly where the difference lies, if it is in fact filter context causing the difference.

[Phil_Seamark]

Cool.  It might not be filter context in this case but I have a feeling it might be that (essecially the calcuation order of the various objects)

[jeffreyweir]

Here's another attempt to upload an image of the report:

As you can see, for the first ID, the 'Longest Stretch" this person worked is 9 days straight, as evidenced by the sparklines to the right where you can see a 9 day run that spans the end of the 'Historic' chart and continues into the 'Forecast' chart.

You can also see at the start of the 'Historic' chart that there is a stretch that has been cut off. The person concerned had worked 11 days straight by the time that run ended, but as per my business requirement I only wanted to report on the maximum run that started within the window of interest. And my calculated column approach correctly ignores this 11 day run, and reports on the 9 day run as the maximum stretch.

But my measure doesn't. Here's a screenshot of the PivotTable that has the calc column in as well as the measure:

As you can see, both the calc column and the measure return the correct result for 'FirstDayOff', but the LongestStretch Measure returns 11, and I think that's because the measure is somehow ignoring the 'FirstDayOff' measure that I want it to use, to filter that initial run out.

[jeffreyweir]

I neglected to say that in those sparklines, dark grey denotes days off, light grey denotes workdays.