Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
2 years ago
Solved

WeekNum vs Month

Hello, am looking for column formula. If week number continuous to next month I need to get previous month.   Link: Sample file    Regards, Chandrashekar B  
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Chandrashekar 

     

    I added the comment:

    Column = 

VAR _A = CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = EARLIER ( 'Table'[Week] ) )
    )
    //Get the min date of every week, for examnple, the 16th week is 2024-04-15
RETURN FORMAT(_A,"MMM-YY")
//format the column to "MMM-YY"

     The outcome of variable _A:

     

     

    Best Regards

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

Chandrashekar's avatar
Chandrashekar
Resolver III
2 years ago

Hello,

 

Please check below link.

 

Sample File: Sample File 

 

Regards,

Chandrashekar B

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Chandrashekar 

 

Please try this:

Create a calculated column:

Column =
FORMAT (
    CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = EARLIER ( 'Table'[Week] ) )
    ),
    "MMM-YY"
)

The result is as follow:

 

Best Regards

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

  • Chandrashekar's avatar
    Chandrashekar
    Resolver III
    2 years ago

    Thank you it is working perfectly. Can you please explain how it works in steps please.

     

    Regards,

    Chandrashekar B

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Chandrashekar 

       

      I added the comment:

      Column = 

VAR _A = CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = EARLIER ( 'Table'[Week] ) )
    )
    //Get the min date of every week, for examnple, the 16th week is 2024-04-15
RETURN FORMAT(_A,"MMM-YY")
//format the column to "MMM-YY"

       The outcome of variable _A:

       

       

      Best Regards

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