Forum Discussion

Frequent Visitor

5 years ago

Solved

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

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

Tutorial Requests

AllisonKennedy
5 years ago
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.

6 Replies

AllisonKennedy
Community Champion
5 years ago
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-power-bi
- adityo
  Frequent Visitor
  5 years ago
  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
    Community Champion
    5 years ago
    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/