Forum Discussion
HKIndia
Helper I
Helper I3 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...
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
Ahmedx
Super User
Super Userpls 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
HKIndiaHelper I
Helper IAhmedx 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])