undefined

[RekhaSurya]

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 

•  

[amitchandak]

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

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