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

[iDataDrew]

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.

Index	Age	Min	Max
1	1st Yr	0.5	1
2	2nd Yr	1	2
3	3rd Yr	2	3
4	4th Yr	3	4
5	5th Yr	4	5
6	6th Yr	5	6
7	7th Yr	6	7

Dealer	Vin	Date of Sale
Dealer A	A1	11/8/2009
Dealer A	A2	3/7/2009
Dealer A	A3	18/09/2009
Dealer A	A4	11/8/2009
Dealer A	A5	11/8/2009
Dealer A	A6	4/9/2009
Dealer A	A7	8/5/2009
Dealer A	A8	30/04/2009
Dealer A	A9	27/08/2009
Dealer A	A10	4/5/2009
Dealer A	A11	8/10/2009
Dealer A	A12	30/06/2009
Dealer A	A13	12/6/2009
Dealer A	A14	31/08/2009
Dealer A	A15	2/9/2009
Dealer A	A16	22/04/2009
Dealer A	A17	3/9/2009
Dealer A	A18	2/10/2009
Dealer A	A19	17/07/2009
Dealer A	A20	15/09/2009
Dealer A	A21	25/08/2009
Dealer A	A22	23/12/2009

[v-yulgu-msft · Accepted solution]

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