Selecting the last 7 days based on a calculated column

[Anonymous]

Hi all! Thank you for the response to my previous question: However, I am now unsure of how to select the last 7 days since the last occurence of "Yes" in the L7D Deferment column.  The dax for this column is:

CALCULATE( iF(ISBLANK(sum(Table[Sales]) ),"No","Yes"),CROSSFILTER('Date'[Date],Table[Date],Both))

.

In this case I would like an additional column that again contained "Yes" for the dates 25th Dec - 31st Dec (beacuse the last "Yes" was on Dec 31) and "No" for all others. This would allow me to use that column as a filter. Thank you for any help, much appreciated!

Please find a sample pbix file here: https://1drv.ms/u/s!Aqdo0kKbD08XmDl-k0bRGu2ypprU?e=kXmosa

I would expect an additional column in the date column showing the the most recent 7 days where the isL7D? column is "Yes".  This is shown in the "Target" column below:

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous]

Hi @v-diye-msft, good idea! Please find a sample pbix file here: https://1drv.ms/u/s!Aqdo0kKbD08XmDl-k0bRGu2ypprU?e=kXmosa

I would expect an additional column in the date column showing the the most recent 7 days where the isL7D? column is "Yes".  This is shown in the "Target" column below:

Any help would be much appreciated, thank you.

[amitchandak]

@Anonymous , Try a new column like

column =
var _1 = max(Table[Date])
return
if(Table[Date] >=_1-7 && Table[Date]<=_1,"Yes","No")

[Anonymous]

@amitchandak do you perhaps have any ideas on my updated question? I have added additional data and an example pbix. Thank you very much.

[Anonymous]

Hi @amitchandak thank you for your response. However, this simply returns "Yes" for the last seven days from the max date in the column. I am looking for the date from which the seven days are to be calculated to be dependent on the "L7D Deferment" column.

[amitchandak]

@Anonymous , Like this ?

column =
var _1 = maxx(filter(Table[L7D Deferment]="Yes",Table[Date])
return
if(Table[Date] >=_1-7 && Table[Date]<=_1,"Yes","No")

[Anonymous]

@amitchandak : if I do that a circular dependency is created because "L7D Deferment" is a calculated column. 

column = 
var _1 = maxx(filter('Date Table',[isL7D?]="Yes"),'Date Table'[Datekey])
return
if('Date Table'[Datekey] >=_1-7 && 'Date Table'[Datekey]<=_1,"Yes","No")