Get Current Week based on two conditions (Max Year and Max Week) based on selected filter

[adityo]

Hi,

I wanto to created Current Week based on the Max Selected Year and Month, and the previous Week based on the Min Selected Year and Month. like this

i already created measure for Current week like this :

Current Week =

Var _Max = MAXX(allselected(AP_Database[Week]), AP_Database[Week])

RETURN

CALCULATE(SUM(AP_Database[Amount in local cur.]), filter(AP_Database,AP_Database[Week]=_max))

and 

Previous Week =

Var _Min = MINX(allselected(AP_Database[Week]), AP_Database[Week])

RETURN

CALCULATE(SUM(AP_Database[Amount in local cur.]), filter(AP_Database, AP_Database[Week]=_min))

but the result comes like this

[AllisonKennedy]

@adityo You'll need to create a Week Offset or Year Week merged column that tells Power BI to put Week 53 of the previous year before Week 1 of this year. Radacad have a simple starting script to help with this https://radacad.com/offset-columns-for-the-date-table-flexibility-in-relative-date-filtering-for-pow... 

[adityo]

Hi @AllisonKennedy 

could you give example how to do this, or could you modify from my measure, because I'm sure, the data model is different.

Thank you

[AllisonKennedy]

@adityo  Yes, the data model is different, that's the issue. You need to add a column to your data model. You need to build a date table. The link I provided has a link to download a txt file you can use for the date table you'll need. 

Here's my blog on why you need Date table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html but it doesn't include the Offset column you need. 

So download the M advanced editor from here: https://radacad.com/download/13519/ 

[adityo]

Hi @AllisonKennedy ,

Im totally newbie here, here the screenshot what i do,

i created the table and linked it, what is the next steps?

[AllisonKennedy · Accepted solution]

@adityo Well done, you're getting close. You'll need to add a Week Number column to your date table also, this can be done in Power Query. 

Then, see if this blog helps: Calculating Weekly Sales w/DAX In Power BI | Enterprise DNA

Finally come back and let me know how you're getting on and what further questions you still have. 

[adityo]

thank you so much @AllisonKennedy it works perfectly 👍🏻