Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
blytonpereira
Helper II
Helper II

Lookup to Select the latest date for a card visual

I have the following three tables

Table JNJ has several duplicates for the FC Version column. However, there can only be two distinct values, i.e. in this case it is 2018M02 and 2018M03.

Table UniqCalendar has only unique rows for both columns Period and First Date

Table Calendar has only unique rows of dates

 

Table: JNJ                                                   Table: UniqCalendar                                                    Table: Calendar

Column:    Region    FC Version                   Column  Period           First Date(mm,dd,yyyy)          Column:    Date (mm,dd,yyyy)

                 ASPAC     2018M02                                   2018M01      01/01/2018                                       01/01/2018

                 EMEA       2018M03                                   2018M02      02,01/2018                                       02/02/2018        

                 AMER       2018M03                                   2018M03      03/01/2018                                      03/03/2018

                 EMEA       2018M02                                   2018M04      04/01/2018                                       04/01/2018

                                                                                    2018M05     05/01/2018                                        05/01/2018

                                                                           .....2020M12          12/01/2020                             .............12/01/2020                 

 

I would like to write a measure such that it will read the most recent date in the FC Version column.

For example 2018M03 is the most recent so the measure would LOOKUP to the UniqCalendar table and see that 2018M03 is infact March 01 2018.

The measure will also be able to look up the previous month from the FC Version column, i.e. 2018M02 which represents February 1st 2018.

 

The measure will be used as a card and the OUTPUT should look like

"March vs February 2018". So the measure should dynamically be able to print the Latest month, Previous MONTH-1, Latest Year based on what values it can see in the FC Version column.

 

The relationships look like belowsave.JPG

 

 

1 ACCEPTED SOLUTION

Hi @blytonpereira,

 

I hope you didn't share anything confidential here.

 

My formula works. The cause is the relationship. The column of the relationship in JNJ table is [period] while it's [Fc Version] in your first post. The easiest solution based on current work is as follows.

1. Create an inactive relationship. 

2. Modify the measure.

Measure =
VAR temp =
    ADDCOLUMNS (
        'jnj mi_stat_cons_dho',
        "Year", YEAR (
            CALCULATE (
                MAX ( UniqCalendar[FirstDate] ),
                USERELATIONSHIP ( 'jnj mi_stat_cons_dho'[FC VERSION], UniqCalendar[Fin YearMonthM] ),
                ALLEXCEPT ( 'jnj mi_stat_cons_dho', 'jnj mi_stat_cons_dho'[FC VERSION] )
            )
        ),
        "Month", FORMAT (
            CALCULATE (
                MAX ( UniqCalendar[FirstDate] ),
                USERELATIONSHIP ( 'jnj mi_stat_cons_dho'[FC VERSION], UniqCalendar[Fin YearMonthM] ),
                ALLEXCEPT ( 'jnj mi_stat_cons_dho', 'jnj mi_stat_cons_dho'[FC VERSION] )
            ),
            "mmmm"
        )
    )
VAR latestYear =
    MAXX ( temp, [Year] )
VAR months =
    DISTINCT ( SELECTCOLUMNS ( temp, "Month", [Month], "fcversion", [FC Version] ) ) // fcversion for the month order.
RETURN
    CONCATENATE (
        CONCATENATEX ( months, [Month], " vs ", [fcversion], DESC ),
        " " & latestYear
    )

Lookup-to-Select-the-latest-date-for-a-card-visual2

 

 

Best Regards,

Community Support Team _ Dale
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

3 REPLIES 3
v-jiascu-msft
Employee
Employee

Hi @blytonpereira,

 

There aren't any days in the result. So the Calendar table is idle. Try this measure, please.

Measure =
VAR temp =
    ADDCOLUMNS (
        'JNJ',
        "Year", YEAR ( CALCULATE ( MAX ( UniqCalendar[First Date(mm,dd,yyyy)] ) ) ),
        "Month", FORMAT ( CALCULATE ( MAX ( UniqCalendar[First Date(mm,dd,yyyy)] ) ), "mmmm" )
    )
VAR latestYear =
    MAXX ( temp, [Year] )
VAR months =
    DISTINCT ( SELECTCOLUMNS ( temp, "Month", [Month], "fcversion", [FC Version] ) ) // fcversion for the month order.
RETURN
    CONCATENATE (
        CONCATENATEX ( months, [Month], " vs ", [fcversion], DESC ),
        " " & latestYear
    )

Lookup-to-Select-the-latest-date-for-a-card-visual

 

 

Best Regards,

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

@v-jiascu-msft

 

Thank you for your answer. I updated my question slightly.

 

One thing to note, in the column UniqCalendar we do not necessarily have just the exact dates as those in the FC version column from JNJ table.

SO using MAX of date in UniqCalendar will pick the lates date which will show an incorrect result in this case.

If you notice in my updated question you can see UniqCalendar table we have  unique dates upto 2020

 

I attached my .pbix file below

https://drive.google.com/open?id=1dWV8uRd8a0z-3jbMWGkdRy3afdzYvW0r

 

Page name on the report: "DHO"

Hi @blytonpereira,

 

I hope you didn't share anything confidential here.

 

My formula works. The cause is the relationship. The column of the relationship in JNJ table is [period] while it's [Fc Version] in your first post. The easiest solution based on current work is as follows.

1. Create an inactive relationship. 

2. Modify the measure.

Measure =
VAR temp =
    ADDCOLUMNS (
        'jnj mi_stat_cons_dho',
        "Year", YEAR (
            CALCULATE (
                MAX ( UniqCalendar[FirstDate] ),
                USERELATIONSHIP ( 'jnj mi_stat_cons_dho'[FC VERSION], UniqCalendar[Fin YearMonthM] ),
                ALLEXCEPT ( 'jnj mi_stat_cons_dho', 'jnj mi_stat_cons_dho'[FC VERSION] )
            )
        ),
        "Month", FORMAT (
            CALCULATE (
                MAX ( UniqCalendar[FirstDate] ),
                USERELATIONSHIP ( 'jnj mi_stat_cons_dho'[FC VERSION], UniqCalendar[Fin YearMonthM] ),
                ALLEXCEPT ( 'jnj mi_stat_cons_dho', 'jnj mi_stat_cons_dho'[FC VERSION] )
            ),
            "mmmm"
        )
    )
VAR latestYear =
    MAXX ( temp, [Year] )
VAR months =
    DISTINCT ( SELECTCOLUMNS ( temp, "Month", [Month], "fcversion", [FC Version] ) ) // fcversion for the month order.
RETURN
    CONCATENATE (
        CONCATENATEX ( months, [Month], " vs ", [fcversion], DESC ),
        " " & latestYear
    )

Lookup-to-Select-the-latest-date-for-a-card-visual2

 

 

Best Regards,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.