URGENT - Historic Months by MAX week

[mb0307]

Hi, 

This is a completicated request so please see attached pbix file and query is also written inside the file.

Weekly Forecast.pbix 

Please this is an urgent request.

Thanks in advance.

[mb0307 · Accepted solution]

Found a solution on an another forum:

Forecast By Month (Just Max Wk No) =

//This variable stores the selected value. If nothing is selected, then Blank() is stored in the variable.

VAR _WkNo = SELECTEDVALUE('MaxWeeks'[Max Week], BLANK())

//This is a table that summarizes the data and adds a column "_SumWeek" which is the max week if the week number selected is <= Max Week or the selected week if > Max week.

VAR _SumTable =

ADDCOLUMNS(

SUMMARIZE(

FILTER(

'Final_FC',

'Final_FC'[Week Number] <= _WkNo

),

'Final_FC'[Month Year],

'Final_FC'[Week Number],

'Final_FC'[Max Week Number],

"_FCTotal", SUM(Final_FC[Forecast])

),

"_SumWeek",

IF([Max Week Number]<_WkNo,[Max Week Number],_WkNo)

)

//This is the result if a Max Week Number IS NOT selected in the slicer...

VAR _FC =

SUMX(

FILTER(

'Final_FC',

'Final_FC'[Week Number] = 'Final_FC'[Max Week Number]

),

'Final_FC'[Forecast]

)

//This is the result if a Max Week Number IS selected in the slicer...

VAR _SelectedWeekFC =

SUMX(

FILTER(

_SumTable,

[Week Number] = [_SumWeek] &&

[Month Year]='Final_FC'[Month Year]

),

[_FCTotal]

)

RETURN

[v-kelly-msft]

Hi @mb0307 ,

First create a slicer table as below:

Slicer Table = DISTINCT('Date'[Week number])

Then create a measure as below:

Measure = 
var _maxmonth=CALCULATE(MAX('Date'[Month Number]),FILTER(ALL('Date'),'Date'[Week number]=SELECTEDVALUE('Slicer Table'[Week number])))
Return
IF(MAX('Date'[Month Number])<_maxmonth,"WK"&MAXX(FILTER(ALL('Date'),'Date'[Month Name]=MAX('Date'[Month Name])),'Date'[Week number]),"WK"&SELECTEDVALUE('Slicer Table'[Week number]))

Finally you will see:

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mb0307]

@v-kelly-msft  Thanks for looking into my request. 

Your DAX doesn't sum the Forecast by Months and Week Numbers are applied too all the months based on slicer selection.  

I have recreated the pbix file with more clear request, please see the link in original post. 

Thanks once again - please let me know if you have any queries.

[v-kelly-msft]

Hi  @mb0307 ,

Create a measure as below:

Measure = 
var _maxmonth=CALCULATE(MAX('Date'[Month Number]),FILTER(ALL('Date'),'Date'[Week number]=SELECTEDVALUE('Slicer Table'[Week number])))
var _maxweeknumber=CALCULATE(MAX('Date'[Week number]),FILTER(ALL('Date'),'Date'[Month Number]=MAX('Date'[Month Number])&&'Date'[Month Number]<_maxmonth))
Return
IF(MAX('Date'[Month Number])<_maxmonth,CALCULATE(SUM(Final_FC[Forecast]),FILTER(ALL('Date'),'Date'[Week number]=_maxweeknumber)),
CALCULATE(SUM(Final_FC[Forecast]),FILTER(ALL('Date'),'Date'[Week number]=SELECTEDVALUE('Slicer Table'[Week number]))))

And you will see:

For the modified .pbix file,pls see attached.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mb0307]

Thanks a lot for looking into my request and sending me the pbix, but this is not the what I am looking for.

Sorry if I wasn't clear in explaining the output.  This is what I want the output to look like:

The pbix I provided is not with the complete dataset.  In the original dataset I have all the week in a year but I want the result to be based on the MAX Week of the month.

Thanks in advance.
Manoj

[mb0307]

Can someone help with the query please?

[amitchandak]

@mb0307 , Can you please explain the requirement. Or is it written inside pbix ?

[mb0307]

Thanks for your reply - much appreciated. 

requirement is written inside pbix. 

Thanks.