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

How to create a calculated column that references a dynamic date slider?

Here's what I'm working with.  I created a vehicle age measure that updates based on the selection of a date.  The formula for this measure is Adjusted Age = DIVIDE( Selected Date - Date of Sale, 365, 0 ).  That's simple enough. I can plug that measure into the table and it updates based on the selected date.  However, I need to do a count of Vin #'s for age groups (1st Yr, 2nd Yr, etc.).  I need to use this adjusted age for this count (if adjusted age >= min and < max, then count towards that age bin), but I'm not sure how to incorporate measures into such a procedure.  If I could somehow make the adjusted age measure into a calculated column, then it would be rather simple to complete this task.  But I haven't been able to create a calculated column that references the selected date (a dynamic date).  The selected date is the selection of a date from a calendar table.  Whenever I try referencing this selected date in a calculated column, however, it doesn't grab the selected date but the first date in the calendar.  This is because I use FIRSTNONBLANK() or MIN() and I guess the evaluation context of the calculated column throws things off.  Does anyone have any suggestions?  Please let me know if you need clarification.  The top table below is the parameter table and the bottom table is sample data.  I need the adjusted age column in the bottom table.  You can easily create a calendar to test this out.

 

IndexAgeMinMax
11st Yr0.51
22nd Yr12
33rd Yr23
44th Yr34
55th Yr45
66th Yr56
77th Yr67

 

DealerVinDate of Sale
Dealer AA111/8/2009
Dealer AA23/7/2009
Dealer AA318/09/2009
Dealer AA411/8/2009
Dealer AA511/8/2009
Dealer AA64/9/2009
Dealer AA78/5/2009
Dealer AA830/04/2009
Dealer AA927/08/2009
Dealer AA104/5/2009
Dealer AA118/10/2009
Dealer AA1230/06/2009
Dealer AA1312/6/2009
Dealer AA1431/08/2009
Dealer AA152/9/2009
Dealer AA1622/04/2009
Dealer AA173/9/2009
Dealer AA182/10/2009
Dealer AA1917/07/2009
Dealer AA2015/09/2009
Dealer AA2125/08/2009
Dealer AA2223/12/2009
1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: How to create a calculated column that references a dynamic date slider?

Hi @iDataDrew,

 

As there is no relationship between above two tables, it's hard to directly calculate the count of Vin #'s for age groups. So, I tried a workaround that combines these two tables via CROSSJOIN.

 

Suppost above two tables are named as 'Test1' and 'Test2'.

Test3 = CROSSJOIN(Test1,Test2)

Create measures like:

Adjusted Age = DIVIDE( MAX('Dim Date'[Date]) - MAX(Test3[Date of Sale]), 365, 0 )

Age measure=
IF (
    [Adjusted Age] >= 0.5
        && [Adjusted Age] < 1,
    "1st Yr",
    IF (
        [Adjusted Age] >= 1
            && [Adjusted Age] < 2,
        "2nd Yr",
        IF (
            [Adjusted Age] >= 2
                && [Adjusted Age] < 3,
            "3rd Yr",
            IF (
                [Adjusted Age] >= 3
                    && [Adjusted Age] < 4,
                "4th Yr",
                IF (
                    [Adjusted Age] >= 4
                        && [Adjusted Age] < 5,
                    "5 th Yr",
                    IF ( [Adjusted Age] >= 5 && [Adjusted Age] < 6, "6th Yr", "7th Yr" )
                )
            )
        )
    )
)

flag = IF(LASTNONBLANK(Test3[Age],1)=[Age measure],1,0)

Count Vin = CALCULATE(COUNT(Test3[Age]),FILTER(Test3,[flag]=1))

Please refer to the uploaded .pbix file for details.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft v-yulgu-msft
Microsoft

Re: How to create a calculated column that references a dynamic date slider?

Hi @iDataDrew,

 

As there is no relationship between above two tables, it's hard to directly calculate the count of Vin #'s for age groups. So, I tried a workaround that combines these two tables via CROSSJOIN.

 

Suppost above two tables are named as 'Test1' and 'Test2'.

Test3 = CROSSJOIN(Test1,Test2)

Create measures like:

Adjusted Age = DIVIDE( MAX('Dim Date'[Date]) - MAX(Test3[Date of Sale]), 365, 0 )

Age measure=
IF (
    [Adjusted Age] >= 0.5
        && [Adjusted Age] < 1,
    "1st Yr",
    IF (
        [Adjusted Age] >= 1
            && [Adjusted Age] < 2,
        "2nd Yr",
        IF (
            [Adjusted Age] >= 2
                && [Adjusted Age] < 3,
            "3rd Yr",
            IF (
                [Adjusted Age] >= 3
                    && [Adjusted Age] < 4,
                "4th Yr",
                IF (
                    [Adjusted Age] >= 4
                        && [Adjusted Age] < 5,
                    "5 th Yr",
                    IF ( [Adjusted Age] >= 5 && [Adjusted Age] < 6, "6th Yr", "7th Yr" )
                )
            )
        )
    )
)

flag = IF(LASTNONBLANK(Test3[Age],1)=[Age measure],1,0)

Count Vin = CALCULATE(COUNT(Test3[Age]),FILTER(Test3,[flag]=1))

Please refer to the uploaded .pbix file for details.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors