cancel
Showing results for 
Search instead for 
Did you mean: 
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

8 REPLIES 8
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

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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors