Substraction a value selection the earliest date and the latest date

[Anonymous]

Hi, 

I need help please with a dax formula, the idea is to use a slice date selector,  and calculate a value using the earliest date and the last date ( using the slice ) 

Table: Test1

Used_Per  Date

10,00         10/6/2018

12,00          10/7/2018

13,00          10/8/2018

14,00          11/8/2018

30,00           10/9/2018

So the idea would be to calculate substraction used_Per from the erlier date ( 10/6 ) and the latest date (10/9/2018) so i can select in the slicer the desired amount of time.

Thanks!

[AlB]

Hi @Anonymous

I'm not quite sure I understand what you need but let's see if this can be it.

1. Place a slicer with Tables1[Date] on your report choose the dates Between option so that you can easily choose the period of time of your interest.

2. In a Card visual, place this measure:

Used_Per_Diff =
CALCULATE ( VALUES ( Test1[User_Per] ); Test1[Date] = MAX ( Table1[Date] ) )
    - CALCULATE ( VALUES ( Table1[User_Per] ); Table1[Date] = MIN ( Table1[Date] ) )

[Anonymous]

Ok, thanks @AlB

The idea is to calculate the % Growth in the last "X" days. So i want to place a card number with % used, and a date slicer. Select a range of date and review how was the difference beetween that selected dates.

I used your dax and i have this error: " A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed. " 

Thanks.

[AlB]

@Anonymous

OK, try this

Used_Per_Diff =
CALCULATE (
    VALUES ( Test1[User_Per] );
    FILTER ( ALL ( Test1[Date] ); Test1[Date] = MAX ( Table1[Date] ) )
)
    - CALCULATE (
        VALUES ( Table1[User_Per] );
        FILTER ( ALL ( Table1[Date] ); Table1[Date] = MIN ( Table1[Date] ) )
    )

[Anonymous]

Ok, @AlB

PowerBI Shows an error, 

Error Message:
MdxScript(Model) (6, 5) Calculation error in measure 'Filesystem_report_monthly'[Used_Per_Diff]: A table of multiple values was supplied where a single value was expected.

Formulas:


section Section1;

shared Filesystem_report_monthly = let
dbo_Filesystem_report_monthly = VNX{[Schema="dbo",Item="Filesystem_report_monthly"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(dbo_Filesystem_report_monthly,{{"used_mb", Int64.Type}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type1", "Division", each [used_mb] / 1024, type number),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Division",{{"Division", "Used GB"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"free_mb", Int64.Type}, {"total_mb", Int64.Type}}),
#"Inserted Division1" = Table.AddColumn(#"Changed Type2", "Division", each [free_mb] / 1024, type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Division1",{{"Division", "Free GB"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Free GB", Int64.Type}, {"Used GB", Int64.Type}}),
#"Inserted Division2" = Table.AddColumn(#"Changed Type3", "Division", each [total_mb] / 1024, type number),
#"Changed Type4" = Table.TransformColumnTypes(#"Inserted Division2",{{"Division", type number}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type4",{{"Division", "Total GB"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Total GB", Int64.Type}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type5",{{"Used GB", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Used GB"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "used_mb", "used_mb - Copy"),
#"Divided Column" = Table.TransformColumns(#"Duplicated Column", {{"used_mb - Copy", each _ / 1024, type number}}),
#"Renamed Columns3" = Table.RenameColumns(#"Divided Column",{{"used_mb - Copy", "Used GB"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Used GB", Int64.Type}, {"date", type date}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type6", "Used GB", "Used GB - Copy"),
#"Inserted Division3" = Table.AddColumn(#"Duplicated Column1", "Division", each [#"Used GB - Copy"] / [Total GB], type number),
#"Inserted Multiplication" = Table.AddColumn(#"Inserted Division3", "Multiplication", each [Division] * 100, type number),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Multiplication",{"Used GB - Copy"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"Multiplication", "Used_Per"}})
in
#"Renamed Columns4";

[AlB]

@Anonymous

Can you share the pbix, to see exactly what you are trying to do?

[Anonymous]

@AlB

Sure, here it is https://drive.google.com/file/d/1Jmr404HAaURtW4xUzk3e7UCunWrqLhDn/view?usp=sharing 

[AlB]

Hi @Anonymous

The problem is that there are multiple rows with the last date. That is why the measure fails. Which one do you want to use? Or what would you like to do about that?

[Anonymous]

Hi @AlB

Ok, i understand, and there is any posibility to mantein that table? the idea is to order by the latest update info on the File System

[AlB]

@Anonymous

Ok, but what does that mean exactly? I see 629 rows with date 17/12, each row with its own Used_Per value. What would you like to do with those? Similar for the earliest date, 19/10.  

[Anonymous]

Yes, @AlB

All that rows are from a diferent Storage File System, the data is gathering in that way with a PowerShell Script, then is droped into a SQL Database, and then PowerBI grab all the information from te SQL, so.. i create a Threshold value, using a formula to divide the used space and the available space, and show that information in one table. So in order to show only the "highest" values i filter the table with "lastday" option and higher thresheld also. All the rows i think are important. The idea would be to add only that part so if a select a File System i can check the used % Growth in the last "x" Days.

[Anonymous]

Any idea @AlB ? :S 

[AlB]

@Anonymous

I'm afraid I still do not understand what you are trying to get as result. Maybe if you show a clear example...

[Anonymous]

lol, 😛 

@AlB

In the tables i have the row used_per is the result of the division beetween used space and available space. So.. the idea is to select a period of time, let's say  10/11/2018 and 17/12/2018 and check what was the growth of the used percentage. To do that my idea was to substract the used_per of the date 10/11/2018 with the used_per of the 17/12/2018. And the result will be the Difference of the use in that selected time. 

It's clear now ?  

Thank you

[AlB]

@Anonymous

Ok, but the issue remains:  which used_per value do you want from the many there are in your table on date 17/12/2017?? There are like 600 values if I remember correctly on that date.Which one is of interest in your calculation?

That's the crux of the matter. 

[Anonymous]

@AlB

Ok, now i understand your question, 

If you look at the main table wich have the values "File System Name" and "IsLatestUsed%" the idea would be to select any value filter in that table an that value will interact with the dax function in order to show the % growth, so if i select the first value in that table and the date filtering the idea would be that the dax function could interact with the selection of the File System filtered.

[AlB]

@Anonymous

The code for the measure should still be valid but you need to apply a slicer with fs_name and select one fs_name in the slicer. Othrwise the system  will throw an error because the measure is returning multiple values.  

Used_Per_Diff_NewVersion = 
CALCULATE (
    VALUES ( Filesystem_report_monthly[Used_Per] );
    FILTER ( ALL ( Filesystem_report_monthly[date] ); Filesystem_report_monthly[Date] = MAX ( Filesystem_report_monthly[Date] ) )
)
    - CALCULATE (
        VALUES ( Filesystem_report_monthly[Used_Per] );
        FILTER ( ALL ( Filesystem_report_monthly[Date] ); Filesystem_report_monthly[Date] = MIN ( Filesystem_report_monthly[Date] ) )
    )

[AlB · Accepted solution]

@Anonymous

To avoid that error you could do something like:

Used_Per_Diff_NewVersion2 =
IF (
    HASONEVALUE ( Filesystem_report_monthly[fs_name] );
    CALCULATE (
        VALUES ( Filesystem_report_monthly[Used_Per] );
        FILTER (
            ALL ( Filesystem_report_monthly[date] );
            Filesystem_report_monthly[Date] = MAX ( Filesystem_report_monthly[Date] )
        )
    )
        - CALCULATE (
            VALUES ( Filesystem_report_monthly[Used_Per] );
            FILTER (
                ALL ( Filesystem_report_monthly[Date] );
                Filesystem_report_monthly[Date] = MIN ( Filesystem_report_monthly[Date] )
            )
        );
    "Please select a single fs_name"
)

where the measure will be more civilized and will kindly remind you to choose a single fs_name instead of throwing an error at your face

[Anonymous]

@AlB your solution was brilliant, ! thank you !!

[AlB]

@Anonymous

No worries, glad we managed to work it out at last