topic Re: DAX Time Intelligence based on current date ignoring date slicers in DAX Commands and Tips

DAX Time Intelligence based on current date ignoring date slicers

DIACHROMA — Thu, 07 Jan 2021 20:32:55 GMT

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

Re: DAX Time Intelligence based on current date ignoring date slicers

littlemojopuppy — Thu, 07 Jan 2021 23:15:42 GMT

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! 🙂

Re: DAX Time Intelligence based on current date ignoring date slicers

amitchandak — Fri, 08 Jan 2021 01:45:26 GMT

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

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

Re: DAX Time Intelligence based on current date ignoring date slicers

littlemojopuppy — Fri, 08 Jan 2021 03:30:20 GMT

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.

Re: DAX Time Intelligence based on current date ignoring date slicers

amitchandak — Fri, 08 Jan 2021 03:51:50 GMT

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.

Re: DAX Time Intelligence based on current date ignoring date slicers

littlemojopuppy — Fri, 08 Jan 2021 03:58:32 GMT

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

Re: DAX Time Intelligence based on current date ignoring date slicers

amitchandak — Fri, 08 Jan 2021 04:04:50 GMT

littlemojopuppy , I disagree.

Re: DAX Time Intelligence based on current date ignoring date slicers

littlemojopuppy — Fri, 08 Jan 2021 04:17:07 GMT

Of course 🙄