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
Super User

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
Super User

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

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
Super User

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)