Rolling average forecast

[Whitney]

Hi,

I received great assistance on this thread however, I can't figure out how to use the solution to create the forecast I desire in future months.

https://community.powerbi.com/t5/Desktop/Retrospective-rolling-6-month-average-based-on-given-criter... 

The DAX I have used is marked as the solution in the above link. The next step and ultimate goal is to forecast WIP consumption using historical consumption as an indicator (see below picture and highlights for indication of chart and using historical WIP).

The card view is the rolling figure which I'd like to use in the future months highlighted - it is dynamic so it picks up fluctuations in the past 6 month period.

I've tried using this method but it keeps spitting back a number of different errors but it's effectively what I'm trying to achieve for WIP, not sales https://blog.enterprisedna.co/forecasting-techniques-in-power-bi-by-using-dax/ 

Any help is greatly appreciated!

[v-xulin-mstf]

Hi @Whitney,

Try measure as: 

Total WIP hours = 
sumx(filter('Table',[Powertrain_WIP_Relative]="WIP")),'Tracking Sheet'[Build Hours])

Best Regards,
Link

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

[Whitney]

@v-xulin-mstf thanks, this worked for the first calculation however, I'm now getting this error for the WIP forecast:

'an invalid numeric representation of a date value was encountered'

Total WIP Forecast =

IF(ISBLANK([Total WIP Hours]),

CALCULATE([Total WIP Hours],SAMEPERIODLASTYEAR('IDW DimDate'[Date])),

BLANK() )

however, my Date is a date value

[v-xulin-mstf]

Hi @Whitney,

Please check:

Does your date column contain an irregular date format? For example 'Year 9999'.

If the issue persists, you can abandon the time-intelligent function and use the time function instead.

Best Regards,
Link

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

[Whitney]

HI @v-xulin-mstf 

See below - issue still persists. Time intelligence was ticked however, unticked still results in the same error.

[v-xulin-mstf]

Hi @Whitney,

Try measure as:

Total WIP Forecast =
IF(ISBLANK([Total WIP Hours]),
CALCULATE([Total WIP Hours],DATEADD('IDW DimDate'[Date],-1,YEAR)),
BLANK() )

Best Regards,
Link

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

[Whitney]

Hi @v-xulin-mstf still getting the same error: 'an invalid numeric representation of a date value was encountered'

The total WIP hours formula is still working:

Total WIP Hours = SUMX(FILTER('Powertrains Tracking Sheet',[Powertrain_WIP_Relative]="WIP"),'Powertrains Tracking Sheet'[Build Hours])

Powertrain_WIP_Relative =

VAR MaxDate = EOMONTH([CurrentMonth],0)

VAR MinDate = EOMONTH(MaxDate,-7) + 1

VAR IsWip =

IF(

MAX('IDW DimDate'[Date]) >= MAX('Powertrains Tracking Sheet'[Inspected])

&& MAX('IDW DimDate'[Date]) <= EOMONTH(MAX('Powertrains Tracking Sheet'[Planned Assembly Start]) ,-1),

"WIP"

)

return

IF(

MAX('IDW DimDate'[Date]) <= MaxDate && MAX('IDW DimDate'[Date]) >= MinDate,

IsWip

)

[v-xulin-mstf]

Hi @Whitney

You can abandon the time-intelligent function and use the time function instead.

Try measure as:

Total WIP Forecast =
var maxdate=MAX('IDW DimDate'[Date])
var mindate=DATE(YEAR(maxdate)-1,MONTH(maxdate),DAY(maxdate))
return
IF(ISBLANK([Total WIP Hours]),
CALCULATE(
    [Total WIP Hours],
    FILTER(
        'IDW DimDate',
        'IDW DimDate'[Date]<maxdate && 'IDW DimDate'[Date]>mindate
    )
),
BLANK() 
)

Best Regards,
Link

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

[Whitney]

Hi @v-xulin-mstf, this is returning "(Blank)" in card view or nothing in a table. My DimDate table date range from 1/1/1900 -> 31/12/9999 if that makes a difference?

Total WIP Forecast =

var maxdate = MAX('IDW DimDate'[Date])

var mindate = DATE(YEAR(maxdate)-1,MONTH(maxdate),DAY(maxdate))

return

IF(

ISBLANK([Total WIP Hours]),

CALCULATE( [Total WIP Hours], FILTER( 'IDW DimDate', 'IDW DimDate'[Date]<maxdate && 'IDW DimDate'[Date]>mindate ) ),

BLANK()

)

[lbendlin]

"My DimDate table date range from 1/1/1900 -> 31/12/9999 if that makes a difference?"

Consider the implications of this - If you have accidentally left the "Auto Date Time"  feature enabled this will create a date hierarchy for every single of your Date or Datetime fields, and will add about 3 Million ( MILLION !) rows for each of your date/ datetime columns.

Please always use a date range in your calendar table that just about covers your fact dates.

[v-xulin-mstf]

Hi @Whitney

The year '9999' caused the issue.

Best Regards,
Link

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

[Whitney]

Hi @v-xulin-mstf , I have filtered the data in the transformation space to be 1/1/2020 - 31/12/2022. It is a company dataset and I cannot change the source.

This formula is still returning blank after filtering the dates - any ideas?

Total WIP Forecast =

var maxdate = MAX('IDW DimDate'[Date])

var mindate = DATE(YEAR(maxdate)-1,MONTH(maxdate),DAY(maxdate))

return

IF(

ISBLANK([Total WIP Hours]),

CALCULATE( [Total WIP Hours], FILTER( 'IDW DimDate', 'IDW DimDate'[Date]<maxdate && 'IDW DimDate'[Date]>mindate ) ),

BLANK()

)

[v-xulin-mstf]

Hi @Whitney,

Maybe you can try your orginal measure again:

Total WIP Forecast =
IF(ISBLANK([Total WIP Hours]),
CALCULATE([Total WIP Hours],SAMEPERIODLASTYEAR('IDW DimDate'[Date])),
BLANK() )

Best Regards,
Link

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

[lbendlin]

Would you mind mentioning the errors you are experiencing?

[Whitney]

Hi, to get my total WIP hours, I tried to use:

Total WIP hours =
CALCULATE(SUM('Tracking Sheet'[Build Hours]),[Powertrain_WIP_Relative]="WIP")

and it spits back "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

[lbendlin]

Are you on the latest Power BI Desktop ? I thought that logic limitation had been lifted recently.

[Whitney]

Yes, I believe so