DAX Time Intelligence based on current date ignoring date slicers

[DIACHROMA]

Hi!

I had trouble with some metrics to calculate my sales across multiple time periods which will be calculated based on the current date, disregarding date filters.

I would like to be able to filter my [Sales Amount] measure according to the predefined periods below:

Last month
Past year
YTD
P3M
P6M
P12M

Without having to manually select the months in a slicer.

The idea is that users don't need to update the slicer month every month.

How can I do ?

Thank you in advance for your help!

Pauline

[littlemojopuppy · Accepted solution]

Hi @DIACHROMA 

Try these out 🙂

Last Month = 
CALCULATE(
	[Sales Amount],
	PREVIOUSMONTH('Date'[Date])
)

Past Year =
CALCULATE(
	[Sales Amount],
	PREVIOUSYEAR('Date'[Date])
)

YTD =
TOTALYTD(
	[Sales Amount],
	'Date'[Date]
)

P3M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-3,
		MONTH
	)
)

P6M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-6,
		MONTH
	)
)

P12M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-12,
		MONTH
	)
)

This assumes you have a date table named "Date" and the date field in it is named "Date"

Hope this helps!  🙂

[amitchandak · Accepted solution]

@DIACHROMA , To answer your question without selecting a month in slicer.

You have two approches

1. You date table should end at max date of you fact or Today //Calendar/date table is must for time intelligence  -

example : calendar(date(2017,01,01), today())

or

calendar(date(2017,01,01), Max(Table[Date]))

Also dicussed same in deatils Why TI fails - https://www.youtube.com/watch?v=OBf0rjpp5Hw

Or you create a column  (one of two and select this month or current month and save that  -https://www.youtube.com/watch?v=hfn05preQYA

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

Month Type = Switch( True(),
eomonth([Date],0)= eomonth(Today(),0),"Current Month" ,
Format([Date],"MMM-YYYY")
)

Formula you can check in last reply

[littlemojopuppy]

@DIACHROMA The sad truth is that @amitchandak employs a bot to respond to people asking questions.  Almost random answers based on keywords in your question.  I don't...I actually try to help people instead of padding stats.

I offered solutions that I think will work (despite the limited info provided).  If they don't, will work to figure them out.

[amitchandak]

@littlemojopuppy , I have carefully, replied to portion you have not answered. Please check Even I refer to last post "Formula you can check in last reply"

@DIACHROMA , I only replied for Selection issue. "

Without having to manually select the months in a slicer.

The idea is that users don't need to update the slicer month every month.

"

Please carefully read what I replied.  There is not overplap with your answer. There is no BOT here.

[littlemojopuppy]

@amitchandak you have carefully, replied to nothing.  You offer nothing new to solutions and just try to poach stats.

[amitchandak]

@littlemojopuppy , I disagree.

[littlemojopuppy]

Of course 🙄

[littlemojopuppy · Accepted solution]

Hi @DIACHROMA 

Try these out 🙂

Last Month = 
CALCULATE(
	[Sales Amount],
	PREVIOUSMONTH('Date'[Date])
)

Past Year =
CALCULATE(
	[Sales Amount],
	PREVIOUSYEAR('Date'[Date])
)

YTD =
TOTALYTD(
	[Sales Amount],
	'Date'[Date]
)

P3M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-3,
		MONTH
	)
)

P6M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-6,
		MONTH
	)
)

P12M =
CALCULATE(
	[Sales Amount],
	DATESINPERIOD(
		'Date'[Date],
		EOMONTH(TODAY(), -1),
		-12,
		MONTH
	)
)

This assumes you have a date table named "Date" and the date field in it is named "Date"

Hope this helps!  🙂