Max Week of every historic month

[mb0307]

Hi,

I download Sales data every week.  This data shows downloaded week's month to all future months of the year.   To see the historic MONTHLY data I need to go back to previous MAX Week downloads of relevant months.

Example: 

1)   Download of Week 31(July's week), will have Sales Forecast data for JULY to DECEMBER 2020.

2)   To see the historical data from JAN to JUN, I have to find the MAX WEEK download of each previous month and then put them together to complete my yearly Sales Forecast.

I want a user to select any particular week and it should show Historic Months (point 2 above) and Future Months (point 1 above) in a chart.

Is it possible to create a measure like this please? 

Thanks  

[mb0307 · Accepted solution]

I have managed to solve this.  Thanks for your help.

Weekly Forecast =

CALCULATE(

SUM(ForecastTable[Forecast]),

FILTER(ForecastTable, ForecastTable[Week Number] = MAX(ForecastTable[Week Number]))

)

[lbendlin]

"I download Sales data every week"

Are you keeping these snapshots or are you doing a flush and fill every week?

[mb0307]

I keep snapshots. All merged into one Sales Forecast data.

Thanks. 

[lbendlin]

and when you merge them do you also keep the snapshot date column intact?

[mb0307]

Yes.  Column WEEK NUMBER is saved in the table. 

[lbendlin]

what's preventing you from using that in your measures?

[mb0307]

My experience with dax - I am new.

I don't know how to do this syntactically. 

[lbendlin]

Prepare for a bit of a roller coaster ride.

For the beginning, look at the use of CALCULATE() and learn how its second and further parameters modify the filter context. 

Below is some pseudo code.

Measure = 

var md = calculate (max(snapshot date),all(sales history))

return calculate (something something),snapshot date=md)