Forum Discussion

HKIndia's avatar
HKIndia
Icon for Helper I rankHelper I
3 years ago
Solved

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

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!

 

Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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

     

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      HKIndia
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        Ahmedx
        Icon for Super User rankSuper User
        3 years ago

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

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

        RETURN
        SUMX(_tbl,[3WksAvgNew])