Last N Days

[captainlaw]

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.

[captainlaw]

I'm curious if there's a better way to compose these DAX to filter my last N days from Calendar dim?  Appreciated!

[v-ljerr-msft]

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

[captainlaw]

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?

[Vvelarde]

@captainlaw

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)

[Yuvaraj]

Hi,

I have followed same method but am using Month instead of Day its working fine when i have mentioned the month value as -1 it exactly shows 2 month records but if the value is -2 its pulling all historic data.

Below steps i have followed 

Column created for last 3 months:

Last3Month =
VAR zMaxDate = LASTDATE(all(Sheet1[Date]))
VAR zMinDate = DATEADD(zMaxDate,-2,MONTH)
RETURN
IF(
Sheet1[Date]> zMinDate&&
Sheet1[Date] <= zMaxDate

, 1 , 0 )

New table - for 3 months data only

Last28daysTable = CALCULATETABLE(Sheet1,Sheet1[Last3Month]=1)

I have to pull the last 3 month records alone. 

Regards,

Yuvaraj

[captainlaw]

Thank you all for your help.
I'm getting "blank" value when I use the following DAX formula for my date. I'm trying to return a date a week ago from the latest date using the following formula.

ErrorBlank = dateadd('calendar'[LAWMaxDate],-6,DAY)

The following is returning values :
LAWMaxDate = LASTDATE(ALL('calendar'[LAWCalendarFull]))

Any help is appreciated!

[captainlaw]

Any idea on this blank DAX that I'm experiencing?  I can attach the pbix file if it helps.

[v-ljerr-msft]

Hi @captainlaw,

Could you try the formula below to see if it works.

Measure =
VAR LAWMaxDate =
    LASTDATE ( ALL ( 'calendar'[LAWCalendarFull] ) )
RETURN
    FIRSTNONBLANK (
        CALCULATETABLE (
            DATEADD ( 'calendar'[LAWCalendarFull], -6, DAY ),
            FILTER ( calendar, 'calendar'[LAWCalendarFull] = LAWMaxDate )
        ),
        1
    )

Regards

[captainlaw]

It works!  But I'm not aware of the logic of the "blank" in the previous metric.  Why would it show blank when LAWMaxDate has value?  Does it mean CALCULATETABLE is required?  How do you determine if it is required or not?

[captainlaw · Accepted solution]

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