Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 below
Solved! Go to 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 )
Best Regards,
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 )
Best Regards,
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 )
Best Regards,
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |