DAX of min value, max value, min date , max date and days difference

[mehaboob557]

Hi All,

I am new to PowerBI and i need help from experts like you..

I am connecting MySQL db and geeting the data. 

I am attaching sample data of csv here which was exported from table visual.

I have versions from 0.1 to 2.0(i.e 0.1, 0.2, 0.3 ..... 0.9, 1.0, 1.1, .... 2.0) for each quotations. i.e each quotations can have multiple versions from 0.1 to 2.1

My desired table should be,

1. I want to show only those quoations which have version 1.0 within  version (0.1 to <=1.0) version

•   I want to show minimum version and maximum version(1.0) for each quotation and days between min version date and version 1.0 date

2. I want to show only those quoatations which have version 2.0 within version (1.1 to <=2.0) version

•   I want to show minimum version and maximum version(2.0) for each quotation and days between min version date and version 2.0 date 

Sample output:

1. I want to show the quoations which have version 1.0 within  version (0.1 to <=1.0) version

•   I want to show minimum version and maximum version(1.0) for each quotation and days between min version date and version 1.0 date

Quation#  |  min version | max version|  duration

quote1       0.1                        1.0              10 days

quote3       0.3                        1.0                 3 days

=================================================================================

2. I want to show the quoatations which have version 2.0 within version (1.1 to <=2.0) version

•   I want to show minimum version and maximum version(2.0) for each quotation and days between min version date and version 2.0 date

Quation#  |  min version | max version|  duration

quote2       1.1                        2.0                 4 days

quote3       1.3                        2.0                 8 days

Please share pbix file if you get the output.

Please help me to achieve this. Thanks a ton in advance

[v-sihou-msft]

@mehaboob557

Based on your sample data, I think you can directly create a calculated table like below:

Table =
SUMMARIZE (
    'sample',
    'sample'[quoteNo],
    "min version", MIN ( 'sample'[version] ),
    "max version", CEILING ( MAX ( 'sample'[version] ), 1.0 ),
    "duration", 1
        * ( MAX ( 'sample'[businessDate] ) - MIN ( 'sample'[businessDate] ) )
)

Then you can put max column into a slicer for filtering expectd data. 

[mehaboob557]

Hi @v-sihou-msft ,

Thank you so much for your response with pbix file.

Everything is fine but, only change is,

I should show only those quotations which have version 1.0 within (0.1 to 1.0) and show only those quotations which have for have 2.0within (1.1 to 2.0). If quotations doesn't have 1.0 and versions 2.0, i should not display them

[danextian]

I wrote  a minimal type in calculated column formula. This is the updated one: https://www.dropbox.com/s/hz4liavjn1xn9kt/min%20max%20dates%20and%20versions.pbix?dl=0

[danextian]

Hi @mehaboob557

I created a calculated column and several measurs. Please follow this link for the solution: https://www.dropbox.com/s/hz4liavjn1xn9kt/min%20max%20dates%20and%20versions.pbix?dl=0

[mehaboob557]

Hi @danextian,

Thank you so much for your response with pbix file.

Everything is fine but, only two changes i need,

The versions within 0.1 to 1.0 versions, if the quoteNo has maximum version as 1.0 , then its max date should be shown and the duration should calculate on that.

2. I should show only those quotations which have version 1.0 within (0.1 to 1.0) and same for versions(1.0 to 2.0), quoteNo should have 2.0. or else we should not show other quotations.

In the above image, you can see that this quotation number doesn't have version 1.0. Hence, we should not show quotations which doesn't have 1.0 version (within 1.0 to 1.0 versions) and which doesn't have 2.0 version(within 1.1 to 2.0 versions)

i mean, I should show only those quotations which have version 1.0 within (0.1 to 1.0) and show only those quotations which have for have 2.0within (1.1 to 2.0). If quotations doesn't have 1.0 and versions 2.0, i should not display them

[danextian]

Hi @mehaboob557

My additional step would be to concatenate the version group and quoteno and then point the ALL and ALLECXEPT functionst to that column.

So for Max versionchange it from 

Max Version = 
IF (
    HASONEVALUE ( 'sample'[quoteNo] ),
    MAXX (
        DISTINCT ( 'sample'[VersionGroup] ),
        CALCULATE (
            MAX ( 'sample'[version] ),
            ALLEXCEPT ( 'sample', 'sample'[VersionGroup] )
        )
    )
)

to

Max Version = 
IF (
    HASONEVALUE ( 'sample'[quoteNo] ),
    MAXX (
        DISTINCT ( 'sample'[VersionGroup] ),
        CALCULATE (
            MAX ( 'sample'[version] ),
            ALLEXCEPT ( 'sample', 'sample'[VersionGroup] )
        )
    )
)

I also updated  the dropbox pbix.

[mehaboob557]

Thank you @danextian.

But, i need only those quotations to be displayed which have version 1.0 (within 0.1 to 1.0)

version group (0.1 to 1.0) i should not display because it doesn't have 1.0 version

version group (1.1 to 2.0) i should not display because it doesn't have 2.0 version

Ex:

1. Quote1  have version 0.1, 0.9, 1.8,2.0

quote 1 will display in version group (1.1 to 2.0) because it have 2.0 version

quote 1 will not display in version group (0.1 to 1.0) because it doesn't have 1.0 version

1. Quote2  have version 0.1, 0.9,1.0, 1.8,2.0

quote 1 will display in version group (1.1 to 2.0) because it have 2.0 version

quote 1 will display in version group (0.1 to 1.0) because it have 1.0 version

[MFelix · Accepted solution]

Hi @mehaboob557,

I have look at your data and made the following PBIX file this is based on the following steps:

Make a group based on the version:

This can also be made using DAX but is much easier like this (if you want I can send you the DAX formula).

Create the following Measure:

Duration = 
DATEDIFF (
    CALCULATE (
        MIN ( 'sample'[businessDate] ),
        ALLSELECTED ( 'sample'[businessDate] )
    ),
    CALCULATE (
        MAX ( 'sample'[businessDate] ),
        ALLSELECTED ( 'sample'[businessDate] )
    ),
    DAY
)

Add in a table visual the following fields:

QuoteNo - Don't summarize

Version - First version (rename to min version)

Version - Last version (rename to max version)

Duration

versions(group) - reduce the size of the column until is hidden

Final version:

Add this measure to your table and the add it to the filters on the table result is as presented:

Validation =
IF (
    MAX ( 'sample'[version] ) = INT ( MAX ( 'sample'[version] ) )
        = TRUE (),
    1,
    0
)

Result below:

Regards,

MFelix

[mehaboob557]

Thank you so much @MFelix

It is working for me. 🙂

[danextian]

the second formula should have been

Max Version = 
IF (
    HASONEVALUE ( 'sample'[quoteNo] ),
    MAXX (
        DISTINCT ( 'sample'[VersionGroup and QuoteNo] ),
        CALCULATE (
            MAX ( 'sample'[version] ),
            ALLEXCEPT ( 'sample', 'sample'[VersionGroup and QuoteNo] )
        )
    )
)