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.
Dear all.
I have a problem and did not find something similar.
I have a table with IDs:
ID | State | Date |
223 | 20 | 06.05.2018 |
223 | 24 | 08.05.2018 |
223 | 26 | 11.05.2018 |
223 | 66 | 12.05.2018 |
225 | 20 | 07.05.2018 |
225 | 24 | 09.05.2018 |
225 | 26 | 14.05.2018 |
225 | 66 | 15.05.2018 |
301 | 20 | 09.05.2018 |
301 | 24 | 10.05.2018 |
301 | 26 | 12.05.2018 |
301 | 31 | 13.05.2018 |
301 | 66 | 15.05.2018 |
What I want to have is that I can select a date (e.q. via slicer) and for each ID I get the state (for that specific date or before). That means, selecting the date 13.05.2019 should result in:
ID | State |
223 | 66 |
225 | 24 |
301 | 31 |
Is there any possiblity to realize that?
Thanks a lot in advance and
warm Regards
Martin
Solved! Go to Solution.
Create a calendar table. Then we can use this formula
Please see attached file as well
Measure = MAXX ( TOPN ( 1, FILTER ( table1, [Date] <= SELECTEDVALUE ( 'Calendar'[Date] ) ), Table1[Date], DESC ), [State] )
Create a calendar table. Then we can use this formula
Please see attached file as well
Measure = MAXX ( TOPN ( 1, FILTER ( table1, [Date] <= SELECTEDVALUE ( 'Calendar'[Date] ) ), Table1[Date], DESC ), [State] )
Hi Zubair_Muhammad.
Thanks a lot for your fast solution !! That works perfectly.
May one additional question. The table works perfect. It is also possible to use that measure in a diagramm.
In that example I would like to have for a certain date the number of worflow IDs depending on the measure.
I did try that but I cannot summarize the measure. I thinkt that is possible but that topic is really new for me.
Best regards,
Martin
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |