Modify to pick up data from last data entry.

[matrix_user]

Hello,

Could someone please help me with modifying this DAX to also pick up data from the last date on record I have imported into Power BI should TODAY data is unavilable.

SpecialDates =

VAR _datetable = DateTable

VAR _today = TODAY()

VAR _month = MONTH(TODAY())

VAR _year = YEAR(TODAY())

VAR _thismonthstart = DATE(_year,_month,1)

VAR _thisyearstart = DATE(_year,1,1)

VAR _lastmonthstart = EDATE(_thismonthstart,-1)

VAR _lastmonthend = _thismonthstart-1

VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)

RETURN UNION(

ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1),

ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2),

ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),"Period","Last 7 Days","Order",3),

ADDCOLUMNS(FILTER(_datetable,[Date]>=_thismonthstart),"Period","This Month","Order",4),

ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",5),

ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",6),

ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",7),

ADDCOLUMNS(_datetable,"Period","Custom...","Order",8)

)

Thank you

[amitchandak · Accepted solution]

@matrix_user , Try like

SpecialDates =
VAR _datetable = DateTable
VAR _today = Maxx(allselected(Fact), Fact[Date])
VAR _month = MONTH(_today)
VAR _year = YEAR(_today)
VAR _thismonthstart = DATE(_year,_month,1)
VAR _thisyearstart = DATE(_year,1,1)
VAR _lastmonthstart = EDATE(_thismonthstart,-1)
VAR _lastmonthend = _thismonthstart-1
VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)

RETURN UNION(
ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1),
ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2),
ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),"Period","Last 7 Days","Order",3),
ADDCOLUMNS(FILTER(_datetable,[Date]>=_thismonthstart),"Period","This Month","Order",4),
ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",5),
ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",6),
ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",7),
ADDCOLUMNS(_datetable,"Period","Custom...","Order",8)
)

[matrix_user]

Thanks amitchandak