Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-diye-msft
Community Support
Community Support

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.

 

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.
Anonymous
Not applicable

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.

amitchandak
Super User
Super User

@Anonymous , Try a new column like

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

Anonymous
Not applicable

@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
Not applicable

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.

@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
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.