Date Table with a custom column 21 day cycle.

[amaleranda]

Hi PowerBI Gurus, 

I need to create a column in the date tabel as per below requirements.

"The base reporting cycle, for work metrics is 21 days. This is defined by calendar week number

e.g. (R1)2020 Week 1-3[4/Jan/2020 to 24/Jan/2020] ,

       (R2)2020Week 4-6[25/Jan/2020 to 14/Feb/2020],

       (R3)2020Week 7-9[15/Feb/2020 to 6/Mar/2020], etc

starting at on the first Saturday and progressing through until on third Friday.  this R# should be incrementtal as week goes on for instance 2020Week 10-12 should be R4 and so fourth. 

this R number should reset at the end of a week cycle of a new year too.

Trick is from 1/Jan/2020 to 3/Jan/2020 belongs part to a last 21 day cycle of the year 2019. 

If this does not explain well I can send you sample date tabel.

If anyone can help me with this, I really appriciate your help.

Regards

Amal Eranda

[Greg_Deckler · Accepted solution]

OK, @amaleranda this one is actually quite trivial, all you need is this column:

R = ROUNDUP([Sequentialish]/3,0)

Of course, getting the Sequentialish column took some WORK!!! I had to modify my Sequential quick measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116 to account for you special circumstances. It's not really Sequential any longer but it works for this purpose.

Anyway, this is the column:

Sequentialish = 
VAR MaxWeeks = SUMMARIZE(ALL('Table'),'Table'[Year],"MaxWeek",MAX('Table'[WeekNum]))
VAR MyYear = [Year]
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('Table'[Year],1),ALL('Table'))
VAR __Table = 
    ADDCOLUMNS(
        GROUPBY(
            'Table',
            [Year],
            "__MaxWeek",MAXX(CURRENTGROUP(),[Weeknum])
        ),
        "__Count",COUNTROWS(FILTER(ALL('Table'),'Table'[Year]=EARLIER([Year]) && 'Table'[Weeknum]=[__MaxWeek]))
    )
    VAR __Count = COUNTROWS(FILTER(__Table,[Year]<MyYear && [__Count] < 7))
    VAR __Count2 = COUNTROWS(FILTER('Table',[Year]=MyYear && [Weeknum]=1))
VAR myNum = 
    IF(
        MyYear=firstYear,
        [WeekNum],
        IF([Weeknum]>1 && __Count2 <> 7,
            [Weeknum]-1,
            MyStart+[Weeknum]-__Count
        )
    )
RETURN myNum

I have attached a PBIX file for you.

[amitchandak]

Can you explain with better example? If you are looking for Nonstandard periods, please refer

:

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

[amaleranda]

@amitchandak ,

Plesae see below image

[v-juanli-msft]

Hi @amaleranda 

Check Greg_Deckler's pbix, if his answer really helps you, please mark it as a solution so others may find the answers to this topic quicklly.

If not, please feel free to let me know.

Best Regards

Maggie