Help with custom column

[PaulMac]

Hi

I need to produce a column at show true false for the previous sunday passed.

I have tried the below with now success. I some able to steer me onto the right track.

if [Day Name] = "Sunday" and [Date is in Future] = false and List.Max([Date]) = [Date] then true else false

Many thanks

Paul

[PhilipTreacy]

Hi @PaulMac 

Bit confused.  Your initial qs showed an M query from Power Query but then you said you are trying to do this in Power Pivot - which uses DAX?  

Any chance you can share some sample data and your expected result?

regards

Phil

[amitchandak]

@PaulMac , if you need in dax you can try like (New column )

if('Date'[date] ='Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1, true(), false())

[PaulMac]

Hi @amitchandak

I am doing this in Power Pivot for Excel and adjusted your DAX to suit my table and column names but this produces a new column that shows True for all Sundays in the tables. I would only like the last Sunday to have past to be a True value. so there should only be 1 True value returned.

[v-jingzhang · Accepted solution]

Hi @PaulMac 

I'm not familiar with Power Pivot for Excel so I approached this with M code in Power Query. I think the logic is the same so you could refer to below codes. The key is that the duration between last Sunday and today is always less than 7 days (except that today is Sunday), so I add this to the criteria.

if [Day Name] = "Sunday" and [Date is in Future] = false and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) < 7 then true else false

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.