Retrospective rolling 6 month average based on given criteria

[Whitney]

Hello!

I'm hoping you could help me, please.

I've been tasked to forecast the percentage of WIP that we consume each month. Our workload fluctuates so a hard-coded percentage figure isn't suitable. Instead, we would like to use our 6-month rolling average to capture fluctuations in workload without manually intervening with the calculation.

See excel screenshot for example calcs - each row is a job the orange cells are an example of my current dataset. Stage 1 and Stage 2 typically do not fall in the same month. Our definition of WIP in a given current month = Stage 1 Start <> blank (i.e. it is completed) and Stage 2 start > today. However, in a retrospective month WIP = Stage 1 Start <> blank AND Stage 2 Start > Stage 1 EOMonth Date. It will be WIP every month until the Stage 2 Start falls on the calendar.

Then, when each job is identified as WIP or not, the Stage 2 hours for WIP jobs in each relative month need to be summed for the rolling average. Then, April will use the rolling average of relative months 1-6, May will use relative months 2-7, June will use relative months 3-8 and so on.

I'm really not sure where to start with this whether to create multiple columns for relative months, use one large dax formula with variables, etc.

Hoping that I can learn something about data structure as well as dax assistance with this inquiry as there seem to be a lot of moving parts.

Any help is greatly appreciated!

Cheers,

[v-kkf-msft]

Hi @Whitney ,

I don’t know the structure of your model. I create the following measure.

Is the image below the output you want?

Measure = 
var sum_1 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -1]="WIP")
var sum_2 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -2]="WIP")
var sum_3 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -3]="WIP")
var sum_4 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -4]="WIP")
var sum_5 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -5]="WIP")
var sum_6 = CALCULATE(SUM('Table'[Stage 2 hours]),'Table'[Month relative -6]="WIP")
var NotNullTotal = IF(sum_1<>BLANK(),1) + IF(sum_2<>BLANK(),1) + IF(sum_3<>BLANK(),1) + IF(sum_4<>BLANK(),1) + IF(sum_5<>BLANK(),1) + IF(sum_6<>BLANK(),1)
return DIVIDE( sum_1 + sum_2 + sum_3 + sum_4 + sum_5 + sum_6, NotNullTotal )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitney]

Hi @v-kkf-msft , thank you for the reply. My data does not have the green or no colour cells indicated above, only the orange ones.  I'm trying to calculate the green cells and the proceeding calculations demonstrated. Whether through a column or measure, I'm unsure which is the best route.

Cheers,

Whitney

[v-kkf-msft · Accepted solution]

Hi @Whitney ,

Try the following formula to create measures:

CurrentMonth = MAX('Slicer Table'[Current Month])

relative = 
VAR MaxDate = [CurrentMonth]
VAR MinDate = EOMONTH(MaxDate,-6) + 1
VAR IsWip = 
    IF(
        MAX('Calendar'[Date]) >= MAX('Table'[Stage 1 start])
        && MAX('Calendar'[Date]) <= EOMONTH(MAX('Table'[Stage 2 start]) ,-1),
        "WIP"
    )
return 
    IF(
        MAX('Calendar'[Date]) <= MaxDate && MAX('Calendar'[Date]) >= MinDate,
        IsWip
    )

Measure = 
var sum_1 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],-5),"yyyy mmmm"))
  )
var sum_2 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],-4),"yyyy mmmm"))
  )
var sum_3 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],-3),"yyyy mmmm"))
  )
var sum_4 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],-2),"yyyy mmmm"))
  )
var sum_5 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],-1),"yyyy mmmm"))
  )
var sum_6 = 
  CALCULATE(
    SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),
    FILTER('Calendar','Calendar'[Year_Month] = FORMAT(EDATE([CurrentMonth],0),"yyyy mmmm"))
  )
var NotNullTotal = IF(sum_1<>BLANK(),1) + IF(sum_2<>BLANK(),1) + IF(sum_3<>BLANK(),1) + IF(sum_4<>BLANK(),1) + IF(sum_5<>BLANK(),1) + IF(sum_6<>BLANK(),1)
return DIVIDE( sum_1 + sum_2 + sum_3 + sum_4 + sum_5 + sum_6, NotNullTotal )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitney]

Hi @v-kkf-msft . To confirm, what is the difference between the slicer table and the calendar table, please?

[v-kkf-msft]

Hi @Whitney ,

Modify the measure relative to the following formula:

relative = 
VAR MaxDate = EOMONTH([CurrentMonth],0)
VAR MinDate = EOMONTH(MaxDate,-6) + 1
VAR IsWip = 
    IF(
        MAX('Calendar'[Date]) >= MAX('Table'[Stage 1 start])
        && MAX('Calendar'[Date]) <= EOMONTH(MAX('Table'[Stage 2 start]) ,-1),
        "WIP"
    )
return 
    IF(
        MAX('Calendar'[Date]) <= MaxDate && MAX('Calendar'[Date]) >= MinDate,
        IsWip
    )

The slicer table and the calendar table:

1. The most important thing is that no relationship has been created between the Calendar table and the Slicer table.                    
2. The Calendar table and the Slicer table need at least one day value in each month. In my file, for the convenience of testing, I use a function to automatically generate the date of each day. You can only use the value of any day in each month, because we only need to use the year and month values of the dates in these two tables, and day is not important.
3. You can also set the Calendar table and Slicer table to the same value.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitney]

Hi @v-kkf-msft , thank you. I got the relative calculation to work however, my measure is coming back blank. My date table only has a MonthYear column, not a Yearmonth column - not sure if this could be why?

Measure =

var sum_1 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-5),"mmmm yyyy"))

)

var sum_2 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-4),"mmmm yyyy"))

)

var sum_3 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-3),"mmmm yyyy"))

)

var sum_4 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-2),"mmmm yyyy"))

)

var sum_5 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-1),"mmmm yyyy"))

)

var sum_6 =

CALCULATE(

SUMX(FILTER('Table',[relative]="WIP"),'Table'[Stage 2 hours]),

FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],0),"mmmm yyyy"))

)

var NotNullTotal = IF(sum_1<>BLANK(),1) + IF(sum_2<>BLANK(),1) + IF(sum_3<>BLANK(),1) + IF(sum_4<>BLANK(),1) + IF(sum_5<>BLANK(),1) + IF(sum_6<>BLANK(),1)

return DIVIDE( sum_1 + sum_2 + sum_3 + sum_4 + sum_5 + sum_6, NotNullTotal )

[v-kkf-msft]

Hi @Whitney ,

Is the format Date in your MonthYear column?

[Whitney]

@v-kkf-msft It was not. I just changed it to a date format to try it returned this error: 

"Couldn't load the data for this visual" MdxScript(Model) 8,26) Calculation error in measure 'IDW DimDate'[Measure]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

[v-kkf-msft]

Hi @Whitney ,

What is the data format of your [CurrentMonth]?

Could you pleasure share screenshots of sample data for the [CurrentMonth] and MonthYear column?

Best Regards,
Winniz

[Whitney]

@v-kkf-msft    

[v-kkf-msft]

Hi @Whitney ,

I guess that the values on the two sides do not match, which caused null value.

Create the following measure to test. If the return value is 1, it means that the expression is correct. If the return value is empty, which means that the values on the two sides are not equal.

Test = CALCULATE( 1, FILTER('IDW DimDate','IDW DimDate'[MonthYear] = FORMAT(EDATE([CurrentMonth],-5),"mmmm yyyy")))

Best Regards,
Winniz

[Whitney]

@v-kkf-msft, I am now getting the below error, both the MonthYear and CurrentMonth formatting is per the screenshots above

"DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

[Whitney]

@v-kkf-msftI copied your date expression for Year_Month and everything fell into place and is working now. Thank you very much for the patience and help!

[Whitney]

@v-kkf-msft Hi, this is possible stretching the topic of this thread. What I would like to do is add the value of my measure to a stacked bar chart in this format. It doesn't appear possible from what I'm reading unless I make all of my different status' a measure instead of a column value. Do you have a different view on this? Thanks again, Whitney

[amitchandak]

@Whitney , seems a similar approach to HR blog. here  current employee is like WIP

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Refer: https://www.youtube.com/watch?v=e6Y-l_JtCq4

[Whitney]

Hi @amitchandak thank you. I have tried to replicate this using my data however, I am getting this error "an invalid numeric representation of a date value was encountered" on the following two formulas (these are the last 2 in the example you have provided)

Last Period WIP =

var _min_date = minx(all('IDW DimDate'),'IDW DimDate'[Date])

var _Expression=if(ISFILTERED('IDW DimDate'[MonthYear]),maxx('IDW DimDate',DATEADD('IDW DimDate'[Date],-1,MONTH)),maxx('IDW DimDate',DATEADD('IDW DimDate'[Date],-1,YEAR)))

Return

CALCULATE(COUNTx(FILTER('Engines Tracking Sheet','Engines Tracking Sheet'[Inspected]<=_Expression && 'Engines Tracking Sheet'[Inspected]>=_min_date && (ISBLANK('Engines Tracking Sheet'[Planned Assembly Start]) || 'Engines Tracking Sheet'[Planned Assembly Start]>_Expression)),('Engines Tracking Sheet'[Work Order Number])),CROSSFILTER('Engines Tracking Sheet'[Inspected],'IDW DimDate'[Date],None))

WIP % Change = IF(NOT(ISBLANK([Last Period WIP])),CALCULATE((DIVIDE([CurrentWIP],[Last Period WIP])-1)*100))

Would you be able to assist why the error exists?

Many thanks,

Whitney