Forecast Future Date

[Anonymous]

Hi all, second time posting and I hope I have the correct forum. I have been battling this calculation for weeks now with no forward traction. I just cant take it anymore! Please help! I've read so many post on this forum on how to solve, I feel as if I know some of you! 🙂  

My scenario.. I have a date table, I have a fact table with non continous dates. I am attempting to fill in blank values with the average of the last three values. I am creating barcharts for my forecast not a table. although neither is working. I do have a relationship between fact and datetable by date. I do not have a full years worth of data and my calculations are by week (start date on Sunday. The issue is, I can get the calc to work for dates that currently have data. I dont need the values to be available for dates that have actual values. If I slice to a date (datetable [start of week] that has no values,then the column is completely blank. Here is my last attempt... I'm dizzy at this point trying different things. Please help!!

DT Forecast =

  If (ISBlank(ResBasicsPlanCombo[Entered Hrs.]),VAR FVD= min(ResBasicsPlanCombo[Week Starting]) VAR CurrentDate =

      CALCULATE(

         LASTNONBLANK('ResBasicsPlanCombo'[Week Starting],ResBasicsPlanCombo[EnteredHrs.]),

     ResBasicsPlanCombo[Week Starting]< FVD)

     VAR Assign= Calculate([FinalHrs],ResBasicsPlanCombo[Week Starting]>=CurrentDate)

Return Assign

[DataInsights]

@Anonymous, would you be able to clarify the result you want? In the example, the average of 40.25 is at the total level. Do you want to calculate this average at the Catcher level or the total level? If you could walk through a few examples and provide a screenshot of the desired output, it would be helpful.

[Anonymous]

@DataInsightsAt the catcher level. Thanks so much for trying to help. I have been pounding my head for weeks now!enshot of example....

[Anonymous]

@DataInsights 

[v-shex-msft]

Hi @Anonymous,

If you share some dummy data with a similar data structure and expected result it should help us clarify your data structure and do test coding formula on these sample data.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng