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 am trying to figure out how to filter data with a measure containing the latest month where data have been uploaded to the pwbi dataset. I want to be able to make by default that the latest month data is choosen and that not everything is taken into account automatically,
Thanks in advance for your help,
Solved! Go to Solution.
@Anonymous So it seems like you want something that I call a Complex Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
In your case it would be something like:
Selector Measure =
VAR __CurrentDate = MAX('Table'[published (date)])
VAR __Table = FILTER('Table',ALLEXCEPT('Table','Table'[id]))
VAR __MaxDate = MAXX(__Table,[published (date)])
RETURN
IF(__CurrentDate = __MaxDate,1,0)
You can then use this measure in the filter pane and filter for 1. __CurrentDate gets the current row's date and compares with the maximum date for that id. If they match, returns 1 otherwise 0. You could also do this as a column.
@Anonymous This looks like Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Or maybe you just need LASTNONBLANK or LASTNONBLANKVALUE, hard to tell without sample data.
Thanks for your answer,
My data looks like this:
published (date) | id | is_something | sales |
saturday 1st february 2020 | 123 | true | 1,000 |
sunday 1st march 2020 | 123 | true | 1,230 |
wednesday 1st april 2020 | 123 | false | 1899 |
And I have a measure containing the maximum date of the current data that have been uploaded, here it would be wednesday 1st April 2020.
As I have hundreds of columns, I do not want make a measure everytime I want to use data the latest data so I was thinking of using filters to show the latest data, this way the report can be actualised automatically when new data is uploaded (normally it should be each month but it can be different times to times) and there are several rows that are each unique within a certain month, an id won't appear two times in the same month
But when I tried to use filters with published date, I saw that the only possibilities are using a fixed date, or a fixed interval (30 days ago for example) and that I couldn't do 'my measure' == 'published' nor make my measure be the default date for the data that would be taken into account on this report.
I don't know if it's clearer now for you, if you have any question please ask.
Thanks for your time,
@Anonymous So it seems like you want something that I call a Complex Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
In your case it would be something like:
Selector Measure =
VAR __CurrentDate = MAX('Table'[published (date)])
VAR __Table = FILTER('Table',ALLEXCEPT('Table','Table'[id]))
VAR __MaxDate = MAXX(__Table,[published (date)])
RETURN
IF(__CurrentDate = __MaxDate,1,0)
You can then use this measure in the filter pane and filter for 1. __CurrentDate gets the current row's date and compares with the maximum date for that id. If they match, returns 1 otherwise 0. You could also do this as a column.
@Greg_Deckler just a quick question I forgot to ask, why make a temporary table filtered containing only the id's ? I may think it's for efficiency but I want to be sure,
Thanks in advance,
@Anonymous It doesn't actually have just id column in the temp table, it is only considering the id column when filtering the table.
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 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |