Forum Discussion
Getting Current Month Dates in DatesMTD
In my datamodel, Date field have future dates. I have created one slicer for date buckets like (MTD, QTD, YTD,Custom Date). When i select MTD it is showing latest month data in data model instead of current month.
Using below dax for creating date bins
Date_filter = UNION(ADDCOLUMNS(DATESYTD(Dim_Date[Date]),"Type","YTD","Order",4), ADDCOLUMNS(DATESMTD(Dim_Date[Date]),"Type","CURRENT MONTH","Order",2), ADDCOLUMNS(DATESQTD(Dim_Date[Date]),"Type","QTD","Order",3), ADDCOLUMNS(CALENDAR(TODAY(),TODAY()),"Type","TODAY","Order",1), ADDCOLUMNS(CALENDAR(MIN(Dim_Date[Date]),MAX(Dim_Date[Date])),"Type","CUSTOM",
Can anyone suggest how to get current month as MTD/ Current Year as YTD in Date bins. Thanks in advance.
pls try this
Date_filter = VAR _toDay = TODAY() VAR _CurrentYear = YEAR(_toDay) VAR _t1 = ADDCOLUMNS ( DATESYTD ( 'Dim_Date'[Date] ), "Type", "YTD", "Order", 4 ) VAR _t2 = ADDCOLUMNS ( FILTER(VALUES('Dim_Date'[Date]),EOMONTH([Date],0) = EOMONTH(_toDay,0) ), "Type", "CURRENT MONTH", "Order", 2 ) VAR _t3 = ADDCOLUMNS ( FILTER(VALUES('Dim_Date'[Date]),YEAR([Date])=_CurrentYear&&QUARTER([Date])=QUARTER(_toDay)), "Type", "QTD", "Order", 3 ) VAR _t4 = ADDCOLUMNS ( CALENDAR ( _toDay, _toDay ), "Type", "TODAY", "Order", 1 ) VAR _t5 = ADDCOLUMNS ( CALENDAR ( MIN ( 'Dim_Date'[Date] ), MAX ( 'Dim_Date'[Date] ) ), "Type", "CUSTOM","Order", 5 ) RETURN UNION(_t1,_t2,_t3,_t4,_t5)pls try this
Date_filter = VAR _toDay = TODAY() VAR _CurrentYear = YEAR(_toDay) VAR _t1 = ADDCOLUMNS ( CALENDAR(DATE(_CurrentYear,1,1 ), EOMONTH(_toDay,0)), "Type", "YTD", "Order", 4 ) VAR _t2 = ADDCOLUMNS (CALENDAR(DATE(_CurrentYear,MONTH(_toDay),1),EOMONTH(_toDay,0)), "Type", "CURRENT MONTH", "Order", 2 ) VAR _t3 = ADDCOLUMNS ( CALENDAR(DATE ( YEAR ( _toDay), FLOOR ( MONTH (_toDay ) -1 , 3 ) + 1 , 1 ),EOMONTH (DATE ( YEAR ( _toDay ), FLOOR ( MONTH ( _toDay ) - 1, 3 ) + 3, 1 ), 0)), "Type", "QTD", "Order", 3 ) VAR _t4 = ADDCOLUMNS ( CALENDAR ( _toDay, _toDay ), "Type", "TODAY", "Order", 1 ) VAR _t5 = ADDCOLUMNS ( CALENDAR ( MIN ( 'Dim_Date'[Date] ), MAX ( 'Dim_Date'[Date] ) ), "Type", "CUSTOM","Order", 5 ) RETURN UNION(_t1,_t2,_t3,_t4,_t5)
7 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Ibendlin, Sorry for the delayed reponse.
With above Dax i'm getting results for current month like below,Type
Order
Date
CURRENT MONTH
2
11/4/2023 12:00:00 AM
CURRENT MONTH
2
11/3/2023 12:00:00 AM
CURRENT MONTH
2
11/2/2023 12:00:00 AM
CURRENT MONTH
2
11/1/2023 12:00:00 AM
but for current month i want dates of ongoing month, same with QTD& YTD,
Type
Order
Date
CURRENT MONTH
2
08/4/2023 12:00:00 AM
CURRENT MONTH
2
08/3/2023 12:00:00 AM
CURRENT MONTH
2
08/2/2023 12:00:00 AM
CURRENT MONTH
2
08/1/2023 12:00:00 AM
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
pls try this
Date_filter = VAR _toDay = TODAY() VAR _CurrentYear = YEAR(_toDay) VAR _t1 = ADDCOLUMNS ( DATESYTD ( 'Dim_Date'[Date] ), "Type", "YTD", "Order", 4 ) VAR _t2 = ADDCOLUMNS ( FILTER(VALUES('Dim_Date'[Date]),EOMONTH([Date],0) = EOMONTH(_toDay,0) ), "Type", "CURRENT MONTH", "Order", 2 ) VAR _t3 = ADDCOLUMNS ( FILTER(VALUES('Dim_Date'[Date]),YEAR([Date])=_CurrentYear&&QUARTER([Date])=QUARTER(_toDay)), "Type", "QTD", "Order", 3 ) VAR _t4 = ADDCOLUMNS ( CALENDAR ( _toDay, _toDay ), "Type", "TODAY", "Order", 1 ) VAR _t5 = ADDCOLUMNS ( CALENDAR ( MIN ( 'Dim_Date'[Date] ), MAX ( 'Dim_Date'[Date] ) ), "Type", "CUSTOM","Order", 5 ) RETURN UNION(_t1,_t2,_t3,_t4,_t5)Hi Ahmed,
Thanks for the answer. This script did work but giving circular dependency error when connecting this table to existing data modelpls try this
Date_filter = VAR _toDay = TODAY() VAR _CurrentYear = YEAR(_toDay) VAR _t1 = ADDCOLUMNS ( CALENDAR(DATE(_CurrentYear,1,1 ), EOMONTH(_toDay,0)), "Type", "YTD", "Order", 4 ) VAR _t2 = ADDCOLUMNS (CALENDAR(DATE(_CurrentYear,MONTH(_toDay),1),EOMONTH(_toDay,0)), "Type", "CURRENT MONTH", "Order", 2 ) VAR _t3 = ADDCOLUMNS ( CALENDAR(DATE ( YEAR ( _toDay), FLOOR ( MONTH (_toDay ) -1 , 3 ) + 1 , 1 ),EOMONTH (DATE ( YEAR ( _toDay ), FLOOR ( MONTH ( _toDay ) - 1, 3 ) + 3, 1 ), 0)), "Type", "QTD", "Order", 3 ) VAR _t4 = ADDCOLUMNS ( CALENDAR ( _toDay, _toDay ), "Type", "TODAY", "Order", 1 ) VAR _t5 = ADDCOLUMNS ( CALENDAR ( MIN ( 'Dim_Date'[Date] ), MAX ( 'Dim_Date'[Date] ) ), "Type", "CUSTOM","Order", 5 ) RETURN UNION(_t1,_t2,_t3,_t4,_t5)
