Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
77 | |
76 | |
43 | |
30 | |
26 |
User | Count |
---|---|
97 | |
91 | |
52 | |
47 | |
46 |