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.
Hello everyone, i have a question. I am taking over a project from another developer and i dont quite understand what is this code doing even after referring to the https://docs.microsoft.com/en-us/powerquery-m/date-functions
Therefore, I also want to find out is there a way to display this result without typing into the power query editor. Thanks.
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows1", each [Transaction Date] <= Date.EndOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()), -1))),
Solved! Go to Solution.
Hi @Anonymous,
In fact, these values aren't static values strings and they are dynamic calculation results based on M query/DAX functions. You cannot directly typing function string in the filter panel.
Regards,
Xiaoxin Sheng
@Anonymous , filter data a week before
Date.From(DateTime.LocalNow()) // today date
Date.AddWeeks(Date.From(DateTime.LocalNow()), -1) // a week before date
end of last week
Date.EndOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()), -1))
so filtering date before last week end date.
You can do that dax, but you can create a flag column or a new tbale
Monday last week end date
Week End date = [Transaction Date]+ 7-1*WEEKDAY([Transaction Date],2) -7
Sunday last week end date
Week End date = [Transaction Date]+ 7-1*WEEKDAY([Transaction Date],1) -7
a new column
if([Transaction Date] <= [Week End date],1,0)
Thanks for the explanation.
Is there a way where can i can show the result visually instead of keying them into the power query? Thanks!
@Anonymous , not very clear, You can have last week end date in dax like
Monday last week end date
Week End date = [Transaction Date]+ 7-1*WEEKDAY([Transaction Date],2) -7
Sunday last week end date
Week End date = [Transaction Date]+ 7-1*WEEKDAY([Transaction Date],1) -7
power query like
Date.EndOfWeek(Date.AddWeeks(Date.From(DateTime.LocalNow()), -1)
what you mean by visually ?
Like this, where i dont really need to type in the advanced editor.
Hi @Anonymous,
In fact, these values aren't static values strings and they are dynamic calculation results based on M query/DAX functions. You cannot directly typing function string in the filter panel.
Regards,
Xiaoxin Sheng
Understood, Thanks!
Hi @Anonymous,
I'm glad to hear this helps. 🙂
BTW, power bi can only extract text values from this input panel so all the inputted 'characters' or 'function names' will be forced escapes /recognized as text values. That is the reason we had to do custom with the filter function itself instead of operating on the 'quick action' panel.
Regards,
Xiaoxin Sheng
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |