Forum Discussion

captainlaw's avatar
captainlaw
Microsoft Employee
9 years ago
Solved

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 t...
Need Help
  • captainlaw's avatar
    captainlaw
    9 years ago

    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

     

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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's avatar
Yuvaraj
Helper I
7 years ago

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