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

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

  • captainlaw's avatar
    captainlaw
    Microsoft Employee
    9 years ago

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