3 weeks start and end date except current week based on weekending slicer

[HKIndia]

Hi All,

Please help..!

Need help with calculating "last3WeeksTotal" based on the selected Weekending date in the slicer. The formula I provided is showing blank results:

I have applied below DAX but it showing blank result :- 

3Wkstotal =

CALCULATE(

    SUM('Date_set'[Revenue]),

    FILTER('Date_set',

    'Date_set'[Order Stage] = "Ordered"

    &&'Date_set'[Scope] = "Order"

    ),FILTER('CalendarDate','CalendarDate'[Date]>=[Selected_date]-27 && 'CalendarDate'[Date]<=[Selected_date]-7))

Report link below:

https://www.dropbox.com/s/6jvwyryae34ormg/3Weeks%20Total%20issue.pbix?dl=0 

Any suggestions or corrections are welcome!

[Ahmedx · Accepted solution]

pls try this

3Wkstotal = 
 VAR _dateselected = [Selected_date]
 VAR _Results =//Monday = 2,Tuesday = 3, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, Sunday = 7 
CALCULATE(
    SUM('Date_set'[Revenue]),
    ('Date_set'[Order Stage] ,
    'Date_set'[Scope])   in {( "Ordered","Order")}, REMOVEFILTERS(CalendarDate),   DATESBETWEEN('CalendarDate'[Date], _dateselected-27,_dateselected-7))
 RETURN
_Results
-----------------or --------------
3Wkstotal = 
 VAR _dateselected = [Selected_date]
 VAR _Results =//Monday = 2,Tuesday = 3, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, Sunday = 7 
CALCULATE(
    SUM('Date_set'[Revenue]),
    ('Date_set'[Order Stage] ,
    'Date_set'[Scope])   in {( "Ordered","Order")},   DATESBETWEEN('CalendarDate'[Date], _dateselected-27,_dateselected-7))
 RETURN
_Results

[HKIndia]

@Ahmedx  Thank you so much, I am about to accept this as solution however i have just a little query, we are summing revenue currentrly right, but if I want to apply average function. it shows wrong result.

How we can achive 4552 when we use Average funtion instead of sum. Please suggest.

[Ahmedx]

it's complicated, but try writing another measure like this

fexed =
var _tbl =
SUMMARIZE('Date_set','Date_set'[Business Unit])

RETURN
SUMX(_tbl,[3WksAvgNew])

[HKIndia]

Thi @Ahmedx  Thank you for replying!

But it is showing blank after applying this measure.! as It seems too comlicated. Thanks for trying!