Forum Discussion
Last N Days
Hello Community,
I created a calculated column to return 1 or 0 based on whether the date is within last 28 days. However, as you see from my screenshot below, it's showing all as 1. Below are all the DAX I'm using to populate this chart. Any pointer where I might have incorrectly implemented the formula?
[DateAxis] - date column format as DateTime
zLast28Days =
IF(
Data_RootTable_ChangeToYourSource[DateAxis] >= Data_RootTable_ChangeToYourSource[zMinDate] &&
Data_RootTable_ChangeToYourSource[DateAxis] <= Data_RootTable_ChangeToYourSource[zMaxDate]
, 1 , 0 )
zMaxDate = LASTDATE(Data_RootTable_ChangeToYourSource[DateAxis])
zMinDate = DATEADD(LASTDATE(Data_RootTable_ChangeToYourSource[DateAxis]),-28,DAY)
On a separate note - If I were to populate this "filtered last 28 day list" as a table, what's the best way to go about it?
Your help is appreciated.
I knew it shouldn't be that hard - firstnonblank, calculatetable - all those are not needed.
Fixed my own trouble simply by switching column to measure.
maxdate=lastdate(all('calendar'[lawcalendarfull]))
mindate=dateadd(lastdate(all('calendar'[lawcalendarfull])),-6,day)
last7=if(calendar[lawcalendarfull]>=calendar[lawmindate]&&calendar[lawcalendarfull]<=calendar[lawmaxdate],1,0
10 Replies
I'm curious if there's a better way to compose these DAX to filter my last N days from Calendar dim? Appreciated!
I'm trying to attach the desktop pbix file for anyone who would like to peek under the hood, however, I'm NOT seeing the attachment option. I see photos and video... where is attachment option within this forum?
Hi,
Last28days = VAR zMaxDate = LASTDATE(all(TableDays[DateAxis])) VAR zMinDate = DATEADD(zMaxDate,-28;DAY) RETURN IF( TableDays[DateAxis] > zMinDate&& TableDays[DateAxis] <= zMaxDate , 1 ; 0 )
And to create a new table with the last 28 days
Last28daysTable = CALCULATETABLE(TableDays,TableDays[Last28days]=1)
Hi captainlaw,
In addition to Vvelarde's solution, you can first add an Index Column for your Calendar dim table on the Query Editor.
Then, you should be able to use the formula below to create the calculate column.
IsLast28days = VAR maxDay = CALCULATE ( MAX ( 'Calendar Table'[Index] ), ALL ( 'Calendar Table' ) ) RETURN IF ( 'Calendar Table'[Index] > maxDay - 28 && 'Calendar Table'[Index] <= maxDay, 1, 0 )And use the formula below to create a new table with the last 28 days mentioned above.
Last28daysTable = CALCULATETABLE ( 'Calendar Table', 'Calendar Table'[IsLast28days] = 1 )
Regards
