A measure based on two columns

[ritanoori]

Hi everyone, 

I have below fact table where it has date col with relation to my calendar table in PBI. 

PERIOD_CALC col is

PERIOD_CALC = EOMONTH('Fact_Table)'[Deal Date],0)

I want to create a measure where to claculate Col AMT B / (S) when Strategy col is "EHA M+1" and when PERIOD_CALC is CM. 

from my screenshot should be Mar 2020. 

It's now totalling up all the amounts when I select Mar in my date slicer. What should I change or what should be added to my measure?

Measure= CALCULATE(sum('Fact_Table'[AMT B / (S)]),'Fact_Table'[Strategy]="EHA M+1")

Noting that PBI will have historical data do when selecting Feb in date slicer, measure result should show only the amounts Feb in Period_Calc colm. 

Thanks a lot

[AiolosZhao]

@ritanoori ,

Thanks for your kudo, and have I solve your questions?

If yes, could you please accept my answer as the solution so other people will know the thread is solved.

If not, you can feel free to tell me what's your question now, and I will glad to help to solve that.

Thanks.

Aiolos Zhao

[amitchandak]

@ritanoori , I did not it completely. But seem like you need to join deal date or period_calc also with date table. That will create an inactive join, Which you can activate using userelation.

Refer how to use it : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[AiolosZhao]

Hi @ritanoori ,

I think your historical data must have a flag or an archive label, something like:

202002 XXX-your Feb data

202003 YYY-your Mar data

If yes, you can create a flag in your dataset, like:

if month(archive label) = month(period_calc) then yes else no.

And you can change your measure to:

Measure= CALCULATE(sum('Fact_Table'[AMT B / (S)]),'Fact_Table'[Strategy]="EHA M+1", flag = "yes")

Then when you select PERIOD_CAL = March, I think it will only show what you want.

If I'm not correct, could you please help to more clarify your question?

Thanks.

Aiolos Zhao