Forum Discussion

matrix_user's avatar
matrix_user
Helper III
4 years ago
Solved

Modify to pick up data from last data entry.

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
DAX
select date
  • amitchandak's avatar
    amitchandak
    4 years ago

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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