topic Re: Selected only the last week of each month in DAX Commands and Tips

Selected only the last week of each month

Anonymous — Tue, 12 Apr 2022 08:00:29 GMT

Hi , I have a column : yearmonthweek, for example 20220414 (2022-04 and week 14).

I want to split this column into 3 and create a measure that returns the last week of each month.

For example 20220417 => "last week of April" etc...

Thank you

Re: Selected only the last week of each month

BeaBF — Tue, 12 Apr 2022 09:23:40 GMT

Anonymous Hi!

Can you paste some sample data and the expected result in explicit way?

Thx,

BF.

Re: Selected only the last week of each month

Anonymous — Tue, 12 Apr 2022 09:52:43 GMT

BeaBF I have 3 columns: Year, Month, Week

I want to return the last week of each months.

For example : Year = 2022 , Month = 04 and Week of april = 13,14,15,16,17

I want to return 04-17

Re: Selected only the last week of each month

BeaBF — Tue, 12 Apr 2022 09:56:50 GMT

Anonymous You have data in this way?

Re: Selected only the last week of each month

Anonymous — Tue, 12 Apr 2022 09:58:44 GMT

Yes ! BeaBF

Re: Selected only the last week of each month

BeaBF — Tue, 12 Apr 2022 10:18:29 GMT

Anonymous ok, so you can do:

Group by in the Power Query, to extract the max week corresponding to each Year-Month:
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Year", "Month"}, {{"Week", each List.Max([Week]), type nullable number}})
in
#"Raggruppate righe"
You can do it directly in the viz by adding the maximum of week:
Last, create a measure like this:

Misura = CALCULATE(MAX(Tabella[Week]), ALLEXCEPT(Tabella, Tabella[Year], Tabella[Month]))

Tell me if there is something unclear, I hope it can help you!

BF.

Re: Selected only the last week of each month

Anonymous — Fri, 15 Apr 2022 05:17:34 GMT

Hi Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure = VAR _maxweek = CALCULATE ( MAX ( 'Table'[week] ), FILTER ( ALL ( 'Table' ), 'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] ) && 'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) ) ) RETURN IF ( MAX ( 'Table'[week] ) = _maxweek, 1, BLANK () )

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Selected only the last week of each month

Anonymous — Fri, 15 Apr 2022 09:02:50 GMT

Anonymous thank you for your answer 😉