Forum Discussion

RekhaSurya's avatar
RekhaSurya
New Member
2 years ago

undefined

Hi There,

 

I have a table which has columns 1. Material 2. Weeknum and 3.Backlog value... I am trying to create a dax formula where in the material in the Max(Calendar week) (-also current week )is repeating in the previous week it should be called as Consecutive if it's not available in immediate previous week but available in other weeks it should be considered as Recurring and anything new for the first time in the current week is called New material. Please help me with Dax formula or any other solution 

  •  
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    RekhaSurya , You need to have a date of the week table with the week rank 

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Before Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<max('Date'[Week Rank])-1))

     

     

    You can create a measure

     

    M1= Switch ( True() ,

    Not(isblank([This Week])) && Not(isblank([Last Week])) , "Consecutive" ,

    Not(isblank([This Week])) && Not(isblank([Before Last Week])) , "Recurring" ,

    "Other"

    )

     

     

    In case you it in filter, you can try segmentation

    Dynamic Segmentation Bucketing Binning
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626


    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k