topic Re: Show the date range as MTD in DAX Commands and Tips

Show the date range as MTD

Anonymous — Thu, 30 Dec 2021 06:45:22 GMT

I want to display a date range slicer as mtd. for Example if today is december 30th the slicer should show a date range from decemeber 1 to december 30th or if today is 2nd january 2022 then it should show 1st and 2nd january date range. BAsically it should display a date range from the start of the current month till current date(today). Can anayone help me ?

Re: Show the date range as MTD

amitchandak — Thu, 30 Dec 2021 07:00:41 GMT

Anonymous , if you have not selected any date then with help from data table and measure on any fact or date table

MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

Display along with date from date table

refer if needed

All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI

In case you select a date from joined date table and then need a mtd tred, then you need independent date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Re: Show the date range as MTD

Anonymous — Thu, 30 Dec 2021 07:11:14 GMT

Hi amit... Thanks for promt reply...

but I want to filter a date slicer to show a date range slicer showing date from 1st of current month till today not any values such as qunatity but just a date range

Re: Show the date range as MTD

wdx223_Daniel — Thu, 30 Dec 2021 07:23:33 GMT

add a calculated column in your date table

new column=if(format([date],"yyyymm")=format(today(),"yyyymm")&&[date]<=today(),1,0)

then use this new column to filter your slicer

Re: Show the date range as MTD

Anonymous — Wed, 09 Feb 2022 08:51:03 GMT

it works fine when there is no date range selected. When we select a date range the column doesnot filter the slicer. Can you suugest an alternative

Re: Show the date range as MTD

Anonymous — Wed, 16 Feb 2022 16:46:47 GMT

Hi wdx223_Daniel

I am too facing same problem but your solution is working when we don't alter the date range.

if we select any date outside the range post this filter applied. date filter is not applying for any values , its just showing complete unfiltered data.