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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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 ?

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks for your reply - much appreciated. 

requirement is written inside pbix. 

Thanks. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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