Forum Discussion

Hemanth_PBI's avatar
Hemanth_PBI
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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)

     

  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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)
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

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)

 

Hemanth_PBI's avatar
Hemanth_PBI
Frequent Visitor
3 years ago

Hi Ahmed,
Thanks for the answer. This script did work but giving circular dependency error when connecting this table to existing data model

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    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)
    • Hemanth_PBI's avatar
      Hemanth_PBI
      Frequent Visitor
      3 years ago

      Thank you. It did work.