Summarized values from Multiple Datasets in to one Matrix ?

Just to clarify my final output must be something like below..Would above steps be sufficient to complete the solution (there are much more columns to consider !)

Good afternoon F_Reh​,

Yes, this is practical. I wouldn't try to join or union the six tables, especially because Table A is always one month ahead.

I'd keep the tables separate and let each KPI determine its own latest available month.

For example, you could have a measure for Table A:

KPI A =
VAR LatestDate =
MAX('Table A'[Date])
RETURN
CALCULATE(
SUM('Table A'[Value]),
'Table A'[Date] = LatestDate
)

Then do the same for B-F, with each measure looking at the latest date in its own table.

You can then create a small KPI table containing:

KPI A
KPI B
KPI C
KPI D
KPI E
KPI F

and use a SWITCH measure to return the corresponding KPI value.

That would allow the Matrix to look something like:

KPI       Latest Month    Value
KPI A     July             xxx
KPI B     June             xxx
KPI C     June             xxx
KPI D     June             xxx
KPI E     June             xxx
KPI F     June             xxx

The key here is that you're not trying to find one common "latest month" for all six datasets. Each KPI is responsible for finding its own latest available date.

If you already have a Calendar table, I'd also use that for the common date filtering, but the "latest available date" logic should still be calculated against each individual KPI table.

So yes, your requirement can be handled in Power BI, and I think keeping the six datasets separate and combining them at the measure/visual level is a better approach than physically merging the data.

Hi F_Reh​,

Thank you for reaching out to Microsoft Fabric Community.

Thank you ShahRukhSameer​ and @danextian for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked?  or let us know if you need any further assistance.

Thanks and regards,
Anjan Kumar Chippa

Hi F_Reh​ ,

Despite ruling out union, i would still recommend you to try that at source end.if you have one single fact table then it become easy to write any timeintelligence calculation using dates.

for union my suggestion will be:

• handle additional columns by inserting null values as columns
• create flag column for latest year,quarter,month using case statement. 

a sample query will look like this:

WITH UnionedTable AS (      SELECT         'A' AS SourceKPI,         A_Month AS Month_Date,         A_Value AS Value,         A_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableA      UNION ALL      SELECT         'B' AS SourceKPI,         B_Month AS Month_Date,         B_Value AS Value,         B_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableB      UNION ALL      SELECT         'C' AS SourceKPI,         C_Month AS Month_Date,         C_Value AS Value,         C_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableC      UNION ALL      SELECT         'D' AS SourceKPI,         D_Month AS Month_Date,         D_Value AS Value,         D_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableD      UNION ALL      SELECT         'E' AS SourceKPI,         E_Month AS Month_Date,         E_Value AS Value,         E_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableE      UNION ALL      SELECT         'F' AS SourceKPI,         F_Month AS Month_Date,         F_Value AS Value,         F_Target AS Target,         CAST(NULL AS VARCHAR(50))  AS ExtraCol1,         CAST(NULL AS VARCHAR(50))  AS ExtraCol2,         CAST(NULL AS DECIMAL(18,2)) AS ExtraCol3     FROM TableF  ),  MaxDatesPerKPI AS (      SELECT         SourceKPI,         MAX(Month_Date) AS MaxDate     FROM UnionedTable     GROUP BY SourceKPI  )  SELECT     u.SourceKPI,     u.Month_Date,     u.Value,     u.Target,     u.ExtraCol1,     u.ExtraCol2,     u.ExtraCol3,     CASE         WHEN u.Month_Date = m.MaxDate THEN 1         ELSE 0     END AS IsLatest FROM UnionedTable u INNER JOIN MaxDatesPerKPI m     ON u.SourceKPI = m.SourceKPI ORDER BY u.SourceKPI, u.Month_Date;

in this we are trying to bring everything from source.so it does not create performance bottlenecks in the long run.problem with going with multiple tables is performance issues while the dataset size grows.

Please give kudos or mark it as solution once confirmed.

regards,

Praful

F_Reh​ 

what about you add a column for each table and set up the specific value for the month that you want to display. 

e.g. Table A

Latest Month Label =

VAR LatestMonth =

MAXX(

ALL('Table B'[date]),

EOMONTH('Table B'[date], 0)

)

RETURN

IF(

EOMONTH('Table B'[date], 0) = LatestMonth,

"Y",

BLANK()

)

Other tables：

Second Latest Month Label =

VAR PreviousMonth =

EOMONTH(MAX('Table B'[date]), -1)

RETURN

IF(

YEAR('Table B'[date]) = YEAR(PreviousMonth)

&& MONTH('Table B'[date]) = MONTH(PreviousMonth),

"Y",

BLANK()

)

then when you do the measure calculation, using the filter to restrict the data.

if this does not work, pls provide some sample data and expected output.

Hi F_Reh​,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked?  or let us know if you need any further assistance.

Thanks and regards,
Anjan Kumar Chippa