Reply
Member
Posts: 117
Registered: ‎08-01-2017
Accepted Solution

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

[ Edited ]

 

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


Accepted Solutions
Super User
Posts: 2,257
Registered: ‎09-19-2016

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

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:

 

groups.png

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:

grouping.png

 

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:

validation.png

 

 

Regards,

MFelix

 



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

Proud to be a Datanaut!




View solution in original post


All Replies
New Contributor
Posts: 469
Registered: ‎10-18-2016

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

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
New Contributor
Posts: 469
Registered: ‎10-18-2016

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

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Moderator
Posts: 2,588
Registered: ‎03-06-2016

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

@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. 

 

22.PNG

 

23.PNG

 

 

Attachment
Member
Posts: 117
Registered: ‎08-01-2017

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

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

 

 

Member
Posts: 117
Registered: ‎08-01-2017

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

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.

 

sample ddd.PNG

 

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.

 

Sample eee.PNG

 

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

New Contributor
Posts: 469
Registered: ‎10-18-2016

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

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.

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
New Contributor
Posts: 469
Registered: ‎10-18-2016

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

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] )
        )
    )
)
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Member
Posts: 117
Registered: ‎08-01-2017

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

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

Super User
Posts: 2,257
Registered: ‎09-19-2016

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

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:

 

groups.png

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:

grouping.png

 

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:

validation.png

 

 

Regards,

MFelix

 



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

Proud to be a Datanaut!