cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Selecting the last 7 days based on a calculated column

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))
 

l7d.JPGas.JPG.

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:

 

Capture.JPG

 

 

 

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Selecting the last 7 days based on a calculated column

@spenot09 , Try a new column like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper IV
Helper IV

Re: Selecting the last 7 days based on a calculated column

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.

Highlighted
Super User IV
Super User IV

Re: Selecting the last 7 days based on a calculated column

@spenot09 , Like this ?

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper IV
Helper IV

Re: Selecting the last 7 days based on a calculated column

@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")

 

Highlighted
Community Support
Community Support

Re: Selecting the last 7 days based on a calculated column

Hi @spenot09 

 

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Helper IV
Helper IV

Re: Selecting the last 7 days based on a calculated column

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:

 

Capture.JPG

 

Any help would be much appreciated, thank you.

Highlighted
Helper IV
Helper IV

Re: Selecting the last 7 days based on a calculated column

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors