cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Eman Frequent Visitor
Frequent Visitor

show the oldest and newest values (disk used) by date not including blank or 0 values

I'm trying to create measures to:

  • First date (Only non-zero/blank diskused)
  • matching first date diskused size
  • last date (Only non-zero/blank diskused)
  • matching first date diskused sizes 
  • Difference between those first and last

 

 

Based on this https://community.powerbi.com/t5/Desktop/Find-value-for-first-and-last-date-by-person/td-p/61984 help I have tried to update those to filter to only the values that are not blank and not zero.

 

The problem is that Last and first are both returning the same values.

 

The measures I'm using:

FirstDiskUsed (sums over server names) =
SUMX (
VALUES( '123Combined_vw_DPM_Disk_Usage_Replica'[ProductionServerName] ),
CALCULATE ( MIN ( '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] ), FILTER( '123Combined_vw_DPM_Disk_Usage_Replica', '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] > 0 && '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] <> BLANK() && FIRSTDATE( '123Combined_vw_DPM_Disk_Usage_Replica'[StartDateTime] ) )
))

 

LastDiskUsed (sums over server names) =
SUMX (
VALUES( '123Combined_vw_DPM_Disk_Usage_Replica'[ProductionServerName] ),
CALCULATE ( MIN ( '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] ), FILTER( '123Combined_vw_DPM_Disk_Usage_Replica', '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] > 0 && '123Combined_vw_DPM_Disk_Usage_Replica'[DiskUsed] <> BLANK() && LASTDATE ( '123Combined_vw_DPM_Disk_Usage_Replica'[StartDateTime] )) )
)

 

Sample data:

 

ProductionServerName,DiskUsed,StartDateTime

server11125253121/1/2016 8:00
server11687879687/1/2018 7:00
server1562626567/8/2018 7:00
server1562626567/15/2018 7:00
server29.91383E+121/1/2014 8:00
server2303312322561/1/2014 8:00
server2472332001281/1/2014 8:00
server22.78859E+123/23/2014 7:00
server21.61244E+117/1/2018 7:00
server28.48197E+127/8/2018 7:00
server2539044249607/8/2018 7:00
server28.48198E+127/15/2018 7:00
server2536695439367/15/2018 7:00
server3 1/1/2014 8:00
server3300369018881/1/2014 8:00
server3463381381121/1/2014 8:00
server33.90742E+123/23/2014 7:00
server3196994007043/23/2014 7:00
server33.93733E+123/30/2014 7:00
server3472060887047/8/2018 7:00
server35.93578E+127/15/2018 7:00
server3526101135367/15/2018 7:00
server3472042577927/15/2018 7:00

 

 

DiskUsedChange (sums over Server Names) =
[LastDiskUsed (sums over server names)] - [FirstDiskUsed (sums over server names)]

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

That's because you need to use MAX instead of MIN in your LastDiskUsed measure.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

That's because you need to use MAX instead of MIN in your LastDiskUsed measure.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
Eman Frequent Visitor
Frequent Visitor

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

Thank you, so glad to have that sorted out after trying for so long and not seeing that simple thing!

Eman Frequent Visitor
Frequent Visitor

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

So, I don't want the largest and smallest numbers, I want the number associated with the oldest date and newest date... does that make sense? Meaning I need to see what the latest number is, and it won't always be the greatest number. Are these queries doing that?

Super User IV
Super User IV

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

That's a good question, I'll have to check on that.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Eman Frequent Visitor
Frequent Visitor

Re: show the oldest and newest values (disk used) by date not including blank or 0 values

Thanks! Look forward to hearing back.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors