Selecting only the last 7 days that aren't null

[Anonymous]

Hi All,

I have a date table to which all other dates in my model are linked. I'd like to create a calculated column on this date table that simply puts "Yes"/"No" depending on whether a value is blank or not in another column in another table. How is this possible as if I try to an "if" statement I can only select measures in other tables, not normal columns. Thank you for any help.

[FarhanAhmed · Accepted solution]

Try creating below calculated column on date dimension.

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

[Anonymous]

@FarhanAhmed  may I also ask how you would modify it so that "Yes" would only be assigned to the last 7 days from the first occurence of data in that other table.

i.e. if in your previous DAX expression the last "Yes" was on 15th May then "Yes" would only be assigned to the dates corresponding to 9th - 15th of May, and "No" to all others.

[amitchandak]

@Anonymous , can be done by creating a column in date table. But try like

Create a table

Tab1 =Summarize(Date,Date[Date],"Sum1",sum(Table[A])+sum(Table[B])+sum(Table1[C]))

And then a column in date table

New Column in Date Table = if(isblank(maxx(filter(Tab1, Tab1[Date] =Date[Date]),Tab1[Sum1])),"No","Yes")

As long as measure are fine with only date filter which is group by it should work for measure