Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nerra
Helper II
Helper II

Filtering on Last complete week

Hi All, 

 

i'm trying to filter my report based on the last completed week. Something like

if max(fiscal week desc) ="Current Week" && Week End Flag="Y" 

then max(fiscal week) else  max(fiscal week)-1

 

 

Has anybody tried something like this. My model is standard star schema. 

Thanks!

1 ACCEPTED SOLUTION

@LivioLanzo

Unfortunately no. 

Can't share it.

 

Anyhow. I think I solved it. I managed to get the last 2 weeks with the metric:

Last2Weeks =
VAR ThisWeek =
CALCULATE (
MAX( 'Fiscal Calendar'[Calendar Date] ),
FILTER (
ALL ('Fiscal Calendar' ),
'Fiscal Calendar'[Week Completed Flag]="Y"
)
)
RETURN
IF(
MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek
,1
,blank()
)  

 

and then I just filtered the visual with the Complete Week Flag='Y' and the Last2Weeks metric to be = 1

 

I'm just wondering fi the second part of the return is correct.   MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek

View solution in original post

9 REPLIES 9
BobBI
Resolver III
Resolver III

Hi Nerra,

 

In your star schema , you must have a Date dimension. Add a new column into  your date dimension called 'IsCurrentWeek'

 

IsCurrentWeek = if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY()),0
                  ,if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY())-1,-1
                     ,if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY())-2,-2,BLANK()
                            )
                       )
                   )

above dax will populate isCurrentweek with  different values , for example ( 0 = Current Week , -1 = previous week as so on)

 

now you can then restrict your report on this field ( either Visual level filter or Page level filter ) as per your requirement.

user 'Advance Filtering'

Show items when the value "is" -1

 

this will restrict your report to show last completed week data. you can tweak the logic to fit in your scenerio.

 

week last.JPG

 

Hope this helps.

 

Good luck

Bob

Thanks you this works well. Just an add on in case if you have many years in time table.

 

Year and week needs to be concatenate. 

 
ISOYearWeek = F_M_Calendar[ISOYear] & " " & FORMAT( F_M_Calendar[ISOWeekNumber], "00")
 
IsCurrentWeek = if( F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21), "00"),0
,if(F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21)-1, "00"),-1
,if(F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21)-2, "00"),-2
)
)
)

 

Hope this helps for someone 🙂

Hi - Your DAX is really helpful, but is there any formula filtering for all completed week. If filter -1,-2 & Blank i am not getting what i am expecting,.. what ever selection i make i see the incompete Weekno 37 (July 15 to July 21), completed week for me is Weekno36.. Can you help me?

Iscurrentweek_issue.JPG

 

 

LivioLanzo
Solution Sage
Solution Sage

Could you share a data sample? @nerra

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Unfortunately not. it's confidential. @LivioLanzo

however, 

i have a matrix showing

week id, store, employee and a sum(hours).

 

i need to filter it for the last complete week. 

Data is loaded every day. WeekEnd is Wednesday. 

 

 

@nerra

 

you cannot 'fake' some of the data ? 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

example.jpgI managed to get some data but it's not quite correct since it's based on yesterday's date.

Last2Weeks =
VAR ThisWeek =
CALCULATE (
MAX( 'Fiscal Calendar'[Calendar Date] ),
FILTER (
ALL ('Fiscal Calendar' ),
'Fiscal Calendar'[Week Completed Flag]="Y"
)
)
RETURN
IF(
MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek
,1
,blank()
)

 

@LivioLanzo

@nerra

 

I meant more the original tables part of the model and along with expected results...

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

@LivioLanzo

Unfortunately no. 

Can't share it.

 

Anyhow. I think I solved it. I managed to get the last 2 weeks with the metric:

Last2Weeks =
VAR ThisWeek =
CALCULATE (
MAX( 'Fiscal Calendar'[Calendar Date] ),
FILTER (
ALL ('Fiscal Calendar' ),
'Fiscal Calendar'[Week Completed Flag]="Y"
)
)
RETURN
IF(
MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek
,1
,blank()
)  

 

and then I just filtered the visual with the Complete Week Flag='Y' and the Last2Weeks metric to be = 1

 

I'm just wondering fi the second part of the return is correct.   MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.