Forum Discussion
DAX help
Hi experts,
I need to develop below measures based on the date slicer and user selection
Last 3 months sales (Based on the user selection)
Current fiscal sales (Based on the user selection)
I want to replace below hard codded formula
Last 3 months sales (Based on the user selection) =
Please help
3 Replies
Anonymous ,
for month using date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))Year
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Rolling 3 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-3,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
or like
Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
"FYTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1,
"MTD",eomonth(_max,-1)+1 ,
"WTD", _max -WEEKDAY(_max,2)+1,
"Cumm", Minx(ALLSELECTED('Date'),'Date'[Date]),
"Rolling 3", date(Year(_max), month(_max) -3, Day(_max))+1,
"Rolling 6", date(Year(_max), month(_max) -6, Day(_max))+1,
"Rolling 12", date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))amitchandak Can you please provide the formula for
Last 3 months sales (Based on the user selection)
I want to replace below hard codded formulaCALCULATE( SUMX(SALES,IF (DATEDIFF( SALES[Posting Date],TODAY(),MONTH) <= 3&& DATEDIFF( SALES[Posting Date],TODAY(),MONTH) >= 1,SALES[Total],BLANK())))
