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

     

captainlaw's avatar
captainlaw
Microsoft Employee
9 years ago

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'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)
  • captainlaw's avatar
    captainlaw
    Microsoft Employee
    9 years ago

    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's avatar
      captainlaw
      Microsoft Employee
      9 years ago

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

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi captainlaw,

         

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

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

         

        Regards

  • 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