Forum Discussion

PaulMac's avatar
PaulMac
Helper IV
5 years ago
Solved

Help with custom column

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] = "S...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

PaulMac's avatar
PaulMac
Helper IV
5 years ago

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's avatar
v-jingzhang
Community Support
5 years ago

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.