Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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 , 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)
)
Thanks amitchandak
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |