Measure to return week number based the current week set in the ERP (Without Dates)

[Anonymous]

Hi all,

I'm fairly new to Power BI & DAX having come from another reporting solution. I've spent hours searching for an an answer to the below without success. Apologies if this is something the search function could have easily resolved (with the right terminology).

Background

Our organisation has 52 week financial years (364 Days in a year), with a 53 week year every sixth or seventh year to make up a week.

As there are no common week start and end dates, our ERP allocates transactions to a unique week number. To complicate matters, the system allows posting to to dates outside of the week date range (which is why I think I can't use date functions).

The unique week number is a function of year and week (E.g. "202051" is FY 2019/2020, Week 51), and is stored in text format.

The ERP has a default week that changes with each weeks financial update process.

The Problem

I want to create a measure equal to the current week number set in the ERP, so that I can filter a table with the measure, and it dynamically updates the report to the same week that is the current default in the ERP.

I.e. Current Week = ERP_PERIOD

Current Week = 202051

The ERP default week is extractable from the system table, and the output will only ever have 1 row, being the set week (below).

ERP_PERIOD (text)

202051

The ERP Date table is extractable, only contains unique values, and has a relationship with the system table in my model.

WEEK_NO (text)	START_DATE	END_DATE
202051	17/06/2020	23/06/2020
202052	24/06/2020	30/06/2020

I'm sure there is a simple solution, and would appreciate any help!

Thank you in advance.

[amitchandak · Accepted solution]

@Anonymous , Not sure I got it. While there can be a way to create it here in power bi. Why don't you bring week and start and end date and merge it with date table.

Week in date table = minx(filter(ErpWeek, Date[date]>=ErpWeek[Start date] && Date[date]<=ErpWeek[End date] ),ErpWeek[Erp Week No])

In the same way you can copy week start and end date also.

Then use Create week Rank based on start date week to deal with this week, last week etc

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

@amitchandakIt looks like I may have been half way there as part of my initial attempts I created a date table in a similar format to the one in your link.

Your guide has the missing pieces (dax formulas) I need to create the measures I need.

I havent yet worked through the steps in your link, but have marked the post as solved as I am certain I can replicate those steps in my model.

Thank you for your help, much appreciated!