CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
Aaron
Aaron

Reputation: 331

Calculated Column that counts from 1-53 per week from specific start date

I am facing a relatively trivial problem. I have a list of start dates for each fiscal year. For example, 03.01.2019 for the 2019 financial year or 30.12.2019 for the 2020 financial year. Now I want the calculated column in my calendar table (Power Pivot) to count up from the start date from 1-53 per week until the next start date.

It would look like this:

Does anyone know how to do this?

Upvotes: 0

Views: 129

Answers (2)

Strawberryshrub
Strawberryshrub

Reputation: 3399

You cen get the ISO 8601 weeknumbers by adding 21 in the optional part. Here a quick example I created. But if you also have dates which start in the middle of the year you should go with a calendar, like @Kosuke Sakai posted:

enter image description here

Upvotes: 1

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

Generally, the recommended approach for this requirement is to prepare a fiscal calendar in the data source (DWH, MDM, Excel, or somewhere), rather than to calculate with DAX.

Having said that, it is possible with DAX.

Assuming you have a table like below. (Let's call it FiscalYears)

FiscalYear

First, you need to add FiscalYear calculated column to your Calendar table with following formula.

FiscalYear :=
VAR CurrentDate = [Date]
RETURN CALCULATE (
    MAX ( FiscalYears[FiscalYear] ),
    FiscalYears[StartDate] <= CurrentDate
)

Then, you can use this to calculate WeekNumberInFiscalYear column.

WeekNumberInFiscalYear :=
VAR StartDate = LOOKUPVALUE (
    FiscalYears[StartDate],
    FiscalYears[FiscalYear],
    [FiscalYear]
)
RETURN DATEDIFF ( StartDate, [Date], WEEK ) + 1

The result will be looking like below.

Result 1 Result 2

Upvotes: 0

Related Questions