Forecasting using the most recent number of weeks

[android1]

Hi,

I use the below which gives me the Forecasted Wks to Breakeven.

I would like to only use the last 4 weeks of Avg Wkly Hrs to forecast the breakeven number of weeks.

Any idea how I can modify for this?

Forecasted Wks to Breakeven = 288/SUM(vw_PivotVisitsInvoiced[Avg Wkly Hrs])

Avg Wkly Hrs = vw_PivotVisitsInvoiced[Tot Hrs Wrkd] / vw_PivotVisitsInvoiced[Wks of Employment]

Wks of Employment = DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK)

[v-jiascu-msft]

Hi @android1,

You can try out the slicer. Can you share a sample your data model please? 

Best Regards,

Dale

[android1]

@v-jiascu-msft Yeah, that would be easy but I need to show 2 columns in a table. One with the last 4 weeks & one with all weeks.

[v-jiascu-msft]

Hi @android1,

Do you have a date table? Maybe you can try it like this.

Forecasted Wks to Breakeven =
VAR currentWeek =
    MIN ( 'calendar'[week] )
RETURN
    CALCULATE (
        288 / SUM ( vw_PivotVisitsInvoiced[Avg Wkly Hrs] ),
        'Calendar'[week] < currentWeek
            &&'Calendar'[week]> currentWeek - 4 )

Best Regards,

Dale 

[android1]

Hi @v-jiascu-msft

That's good. 

I'd like to show Avg Wkly Hrs but only for the 4 most recent weeks.

Can the measure Avg Wkly Hrs = vw_PivotVisitsInvoiced[Tot Hrs Wrkd] / vw_PivotVisitsInvoiced[Wks of Employment] be adapted using somehow?

Calendar'[week] < currentWeek
            &&'Calendar'[week]> currentWeek - 4 )

 So I'd need to only use the last 4 Wks of Employment

[v-jiascu-msft]

Hi @android1,

Yes, you can replace the bold blue part with your old formula. If you want more detailed solution, please provide a dummy sample.

Forecasted Wks to Breakeven =
VAR currentWeek =
    MIN ( 'calendar'[week] )
RETURN
    CALCULATE (
        [Your old measure],
        'Calendar'[week] < currentWeek
            &&'Calendar'[week]> currentWeek - 4 )

Best Regards,

Dale