Forum Discussion

PaulMac's avatar
PaulMac
Icon for Helper IV rankHelper 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] = "Sunday" and [Date is in Future] = false and List.Max([Date]) = [Date] then true else false

 

Many thanks

Paul

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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      PaulMac
      Icon for Helper IV rankHelper 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
        Icon for Community Support rankCommunity 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.

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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