Not Getting Value from another table not related with respect to Year & Month

[Anonymous]

Hi Experts,

I have a scenario like below:

Table 1
Year     Product_Name     Month Profit     Trans_Date
2020     XYZ                     Jan       15000     02/08/2020
2019     ABC                    Aug      16500     02/08/2019
2019     PQR                   DEC       12300      02/04/2019

Note: both table dates has relationship with Date_Table(Dim Table)
Table 2
Year         Sample_Product_Name    Profit    Trans_Date
2020        XYZ                                  1500      02/08/2020
2019        ABC                                  1650      02/08/2019
2019       PQR                                   1230       02/03/2019

Now requirement Kind of 'Actuals and Budgets'
If Transaction Month(Date) is less than current Year & month then
Actuals else Budget

i.e Actuals =Table1[Profit] & Budget =Table2[Profit]

Should i go for Measure or Custom column?

i'm trying this Dax To achieve but not able to get results With respect to each Product name , Year & month.

Custom Column= IF (
YEAR ( Table1[Trans_Date] ) <= YEAR ( TODAY () )
&& MONTH ( Table1[Trans_Date] ) < MONTH ( TODAY () ),
SUM(Table1[Profit]),
SUM(Table1[Profit]))

Kindly Help me with the Proper solution!! 
Advanced Thanks 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.The pbix file is attached in the end.

Table1:

Trans_YearMonth1:

Trans_YearMonth1 = YEAR([Trans_Date])*100+MONTH([Trans_Date])

Table2:

You may create a calculated column or a measure as below.

Calculated Column:

Result column = 
SUMX(
    FILTER(
       Table1,
       [Product_Name]=EARLIER(Table1[Product_Name])
    ),
    IF(
        [Trans_YearMonth1]<YEAR(TODAY())*100+MONTH(TODAY()),
        [Profit],
        CALCULATE(
            SUM(Table2[Profit]),
            FILTER(
                ALL(Table2),
                [Sample_Product_Name]=EARLIER(Table1[Product_Name])
            )
        )
    )
) 

Measure:

Result measure = 
SUMX(
    Table1,
    IF(
        [Trans_YearMonth1]<YEAR(TODAY())*100+MONTH(TODAY()),
        [Profit],
        CALCULATE(
            SUM(Table2[Profit]),
            FILTER(
                ALL(Table2),
                [Sample_Product_Name]=EARLIER(Table1[Product_Name])
            )
        )
    )
)

Result:

Best Regards

Allan

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

[Anonymous]

 Hi Allan @v-alq-msft 
Thank you so much for all your efforts.
Below DAX gave me an idea to achieve my requirement 🙂

[amitchandak]

@Anonymous , Creat common dimesion tables like Date, product and try a measure like this

Sumx(Date,if(Max(date[Date])<=today(), [Actual], [budget]) )

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Hi @amitchandak 
Thanks for your time,
I have created Product as Dim table, but in below mentioned DAX we havn't used Prouct anywhere!
Could you please help further.