Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
Proud to be a Super User!
@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())
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |