DAX help

[Anonymous]

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) =

CALCULATE( SUMX(SALES,

IF (

DATEDIFF( SALES[Posting Date],TODAY(),MONTH) <= 3

&& DATEDIFF( SALES[Posting Date],TODAY(),MONTH) >= 1,

SALES[Total],BLANK()

)))

Please help

[v-stephen-msft]

Hi @Anonymous ,

Is it possible to provide some dummy data and expected results?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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))

[Anonymous]

@amitchandak Can you please provide the formula for 

Last 3 months sales (Based on the user selection)
I want to replace below hard codded formula

CALCULATE( SUMX(SALES,

IF (

DATEDIFF( SALES[Posting Date],TODAY(),MONTH) <= 3

&& DATEDIFF( SALES[Posting Date],TODAY(),MONTH) >= 1,

SALES[Total],BLANK()

)))