how to get calculated column with sum by region in one table by another

[analyticsforall]

Hello! 

I'm really stucked on this subject, hope someone could help (: 

Here is a pbix file https://drive.google.com/file/d/1MUH1ZiFLKCyyNO5fNRDOunY2GAA_vHjY/view?usp=sharing

The question is:

I have 2 tables: PnL and PnL by regions.

Both of them have 2 connections: with calendar and PnL mapping.

In table PnL by regions a have a calculated column called "CIS Rev1", where I need to see all sum from PnL table by same month and year for this region.

For example, for jan 2021 there should ne value per each row = 542 262, but I have only value for 1 day of jan 2021.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached file, if it suits your requirement.

I fixed the DAX formula of the calculated column.

CIS Rev1 =
VAR CurYear = 'Pnl by Regions'[Year]
VAR CurMonth = 'Pnl by Regions'[Month]
RETURN
    SUMX (
        FILTER (
            PnL,
            PnL[PNL name] = "CIS"
                && PnL[Year] = CurYear
                && PnL[Month] = CurMonth
        ),
        PnL[Amount USD]
    )

[analyticsforall]

@Jihwan_Kim this is exactly what I needed!) Thank you! 🤗