last week number as a slicer

[SH-VE]

Hi All  , 

I need to create a report which procides compliance %  for last week.

For this I need help to create a dynamic filter ("last promise week #" here). I am not able to use MAX as date syntax . 

any help, very much appreciated!

 

Thanks,

[mahoneypat]

If you add a Date table, you can make a calculated column like this one to identify the current week.  You can then use it in your measure to filter to just that week (IsCurrentWeek =1).  Or you can use it in the slicer filter.

IsCurrentWeek = var weekstart = TODAY() - WEEKDAY(TODAY()) +1

var weekstop = weekstart +6

return IF('Date'[Date]>=weekstart && 'Date'[Date] <=weekstop, 1, 0)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[SH-VE]

Hi @mahoneypat , I do have a date table in my model. However, for the report , I need the last promise week # as the end result. A promise week # can have two or more dates included .

Also, should a relationship be created with the date table you mentioned for the syntax to work?

I tried creating this syntax:

 

Thanks,

[mahoneypat]

From the picture of your model, your date table was not obvious.  From the pic in your last post, the FILTER function returns a table so can't be used in the return of your measure.  You need to also do an aggregation to return a scalar result.  

Regards,

Pat

[lbendlin]

Do you have a week number column in your Calendar/Dates table?

[SH-VE]

Yes , "promise week" in the snapshot is the week #.

Thanks,

[lbendlin]

A Calendar/Dates table requires contiguous dates across your entire fact date range plus a little more depending on what date math you are doing.  "promise week"  is a fact table attribute.  Required, but not sufficient.  Can you show your data model?

[SH-VE]

Hi @lbendlin , here is snapshot of the data model

 Thanks.

[lbendlin]

that's just one data source in Power Query.  The data model in Power BI shows how your queries (data sources) are related. It's the third icon on the left.

[SH-VE]

Hi @lbendlin ,

I am reusing an original built report. Its huge report with 20 tables, I am only 2 tables from it. please see below