Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mb0307
Responsive Resident
Responsive Resident

URGENT - Historic Months by MAX week

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.

1 ACCEPTED SOLUTION
mb0307
Responsive Resident
Responsive Resident

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

View solution in original post

8 REPLIES 8
mb0307
Responsive Resident
Responsive Resident

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
Community Support
Community Support

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:

Annotation 2020-07-27 133948.png

For details,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

@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.

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:

Annotation 2020-07-27 170629.png

For the modified .pbix file,pls see attached.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

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:

mb0307_0-1595877332653.png

 

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
Responsive Resident
Responsive Resident

Can someone help with the query please?

amitchandak
Super User
Super User

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

Thanks for your reply - much appreciated. 

requirement is written inside pbix. 

Thanks. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors