Selected only the last week of each month

[Anonymous]

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

[v-rongtiep-msft]

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.

[Anonymous]

@v-rongtiep-msft thank you for your answer 😉

[BeaBF]

@Anonymous Hi!

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

Thx,

BF.

[Anonymous]

@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

[BeaBF]

@Anonymous You have data in this way? 

BF

[Anonymous]

Yes ! @BeaBF 

[BeaBF · Accepted solution]

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