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.
Am trying to create a measure which adds the latest values from each site as per the data below. Have tried a number of different formulas but nothing seems to work. Note that not all SiteID enter the information each day but looking to include the value from their last entry.
Data
SubmitTime SiteID PTWAIT
20/12/2018 | 1207 | 0 |
23/12/2018 | 1207 | 0 |
24/12/2018 | 1208 | 2000 |
25/12/2018 | 1208 | 1500 |
20/12/2018 | 1209 | 129 |
23/12/2018 | 1209 | 129 |
24/12/2018 | 1209 | 129 |
25/12/2018 | 1209 | 129 |
20/12/2018 | 1211 | 1738 |
23/12/2018 | 1211 | 1738 |
24/12/2018 | 1211 | 1738 |
25/12/2018 | 1211 | 1680 |
20/12/2018 | 1212 | 0 |
23/12/2018 | 1212 | 0 |
24/12/2018 | 1212 | 0 |
Expected Result for PTWAIT is:
3309 based on
SubmitTime SiteID PTWAIT
23/12/2018 | 1207 | 0 |
25/12/2018 | 1208 | 1500 |
25/12/2018 | 1209 | 129 |
25/12/2018 | 1211 | 1680 |
24/12/2018 | 1212 | 0 |
Solved! Go to Solution.
@CameronS this will do it
Would appreciate Kudos 🙂 if my solution helped.
Measure 3 =
VAR _maxdate =
CALCULATE (
MAX ('Top'[Date] ),
ALLEXCEPT ( 'Top', 'Top'[SiteID] )
)
RETURN
CALCULATE (
SUM ('Top'[PTWAIT] ),
KEEPFILTERS ('Top'[Date]=_maxdate )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@CameronS Please use below measure to get the latest value per site
Measure =
VAR _maxdate = CALCULATE(MAX('Table'[SubmitTime]),ALLEXCEPT('Table','Table'[SiteID]))
RETURN CALCULATE(SUM('Table'[PTWAIT]),'Table'[SubmitTime]=_maxdate)
If it helps accept the solution.
@CameronS this will do it
Would appreciate Kudos 🙂 if my solution helped.
Measure 3 =
VAR _maxdate =
CALCULATE (
MAX ('Top'[Date] ),
ALLEXCEPT ( 'Top', 'Top'[SiteID] )
)
RETURN
CALCULATE (
SUM ('Top'[PTWAIT] ),
KEEPFILTERS ('Top'[Date]=_maxdate )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Almost works - the result I get is the sum of the entries of the most recent date (appears to be filtered to only the most recent date only) - when using a card visual.
Doesn't appear to sum the latest entry for each SiteID, but rather sums the entries for each SiteID for the most recent date only.
Any suggestions?
@CameronS can you explain with example screen shot what you mean?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Result measure =
var lastentrydate = MAX('Table'[Date])
Return CALCULATE (SUM('Table'[PTWAIT]),ALLEXCEPT('Table','Table'[ID]),'Table'[Date]=lastentrydate)
Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
@CameronS Please try below measure
Measure =
VAR _maxDate = CALCULATE(MAX('Table'[SubmitTime]),ALLEXCEPT('Table','Table'[SiteId]))
RETURN IF(MAX('Table'[SubmitTime])=_maxDate,SUM('Table'[PTWait]))
@CameronS here is the output
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |