Forum Discussion

nerra's avatar
nerra
Helper II
7 years ago
Solved

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 we...
Need Help
  • nerra's avatar
    nerra
    7 years ago

    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

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

nerra

 

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

nerra's avatar
nerra
Helper II
7 years ago

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