cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mehaboob557 Member
Member

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

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

10 REPLIES 10
danextian New Contributor
New Contributor

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
danextian New Contributor
New Contributor

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 v-sihou-msft
Moderator

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

 

 

mehaboob557 Member
Member

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

 

 

mehaboob557 Member
Member

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

danextian New Contributor
New Contributor

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
danextian New Contributor
New Contributor

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
Highlighted
mehaboob557 Member
Member

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 4,046 guests
Please welcome our newest community members: