Forum Discussion

Ash1's avatar
Ash1
Frequent Visitor
9 years ago
Solved

Total Storage By Server for Latest date

Hello

 

I'm stuck with an issue on PowerBI desktop. I"m trying to calculate the sum of storage per server for the latest date. 

 

Server Name            VolumeName      totalSize(GB)        DateTime

ServerA                        C:\                      100                       2017-04-06 12 AM

serverA                         D:\                      200                       2017-04-06 12 AM

serverA                         C:\                      100                       2017-04-05 12 AM

serverA                        D:\                      200                       2017-04-05 12 AM

serverB                         c:\                      200                       2017-04-06 12 AM

serverB                         D:\                      200                       2017-04-06 12 AM

serverB                         c:\                      200                       2017-04-05 12 AM

serverB                         D:\                      200                       2017-04-05 12 AM

 

The result I'm looking for is 

ServerName            Total Storage(GB)

ServerA                       300

ServerB                       400      

 

Can anyone help wiht the DAX query            

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Ash1

     

    The cause maybe the time part of the DateTime. If we have two datetimes like this will cause a problem.

    “4/6/2017 12:00:00 AM” and “4/6/2017 12:33:00 AM”.

    So we can add a new column to format the dates. Later we can create a measure with this new column. I hope this would help.

     

    PureDate =
t1[DateTime].[Date]

    TotalStorage(GB) =
CALCULATE ( SUM ( t1[totalSize(GB)] ), LASTDATE ( t1[PureDate] ) )

     

    Best Regards,
    Herbert

  • Ash1's avatar
    Ash1
    9 years ago
    When I looked at the data, I realized that LASTDAY function is resolving to short date. When checked, my dataset has data captured for 2 times in a day but with a different timestamp. After filtering them from the dataset, It was working fine.
     
    Thank you very much for your help..

8 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Ash1

     

    Please have a look at the measure bellow, this could help.

     

    TotalStorage(GB) =
CALCULATE ( SUM ( t1[totalSize(GB)] ), LASTDATE ( t1[DateTime] ) )

     

    Best Regards,
    Herbert

    • Ash1's avatar
      Ash1
      Frequent Visitor
      9 years ago

      Thanks for the reply, 

       

      I tried to create the measure, however I'm running into issues 

       

      Error Message:

      MdxScript(Model) (1, 85) Calculation error in measure 't1[StorageByServer]: A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.

       

      There are no duplicate values in my data. All I have is that Storage details for each drive per server. 

       

      I have tried by creating a datetable and used below measure. I'm getting data but the values appear to be incorrect

       

      StorageByServer = CALCULATE(SUM(t1[totalSize_GB]), LASTDATE(DateTable[Date]))

       

      Any thoughts, where am I going wrong?

       

      ~Ash1

       

       

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Ash1

         

        The cause maybe the time part of the DateTime. If we have two datetimes like this will cause a problem.

        “4/6/2017 12:00:00 AM” and “4/6/2017 12:33:00 AM”.

        So we can add a new column to format the dates. Later we can create a measure with this new column. I hope this would help.

         

        PureDate =
t1[DateTime].[Date]

        TotalStorage(GB) =
CALCULATE ( SUM ( t1[totalSize(GB)] ), LASTDATE ( t1[PureDate] ) )

         

        Best Regards,
        Herbert