How to keep measure's value same over time

[Oopa]

I'm working data which have two fact_tables called fact_stat and fact_dyn. Fact_stat has only one dimensional date (report_date) and fact_dyn has two dimensional dates (report_date & forecast_date). How I can build a measure which gives me results like in the first picture below? Measure "Dyn_all" should keep fact_stat value same over forecast dates. Measures that I'm using: 

Dyn_all = Fact_dyn[Dyn_sum]+Fact_stat[Stat_sum]

Dyn_sum = SUM(Fact_dyn[Amount])

Stat_sum = SUM(Fact_stat[Amount])

With current measures I can see only results:

Fact_dyn table:

Report_Date	Forecast_Date	Amount	Portfolio
30.4.2020	30.4.2020	100	A
30.4.2020	30.4.2020	150	B
30.4.2020	30.4.2020	200	C
30.4.2020	1.5.2020	150	A
30.4.2020	1.5.2020	200	B
30.4.2020	1.5.2020	250	C
30.4.2020	2.5.2020	200	A
30.4.2020	2.5.2020	250	B
30.4.2020	2.5.2020	300	C

Fact_stat table:

Report_Date	Amount	Portfolio
30.4.2020	400	D
15.5.2020	200	D

Dataflow:

[v-kelly-msft]

Hi @Oopa ，

According to your request,you'd better first create an appended table as below:

Then create a dimension table as below:

Table 2 = DISTINCT('Append1'[Portfolio])

Finally create 2 measures as below:

ReplaceToZero = CALCULATE(SUM('Append1'[Amount]),'Append1','Append1'[Portfolio] in DISTINCT('Table 2'[Portfolio]))+0

ReplaceToValue = IF(CALCULATE(COUNTROWS('Append1'),FILTER('Append1','Append1'[Portfolio] in DISTINCT('Table 2'[Portfolio])))=0,MAXX(SUMMARIZE(FILTER(ALLSELECTED('Append1'),'Append1'[Portfolio] in DISTINCT('Table 2'[Portfolio])),'Append1'[Forecast_Date],"measure",CALCULATE(SUM('Append1'[Amount]),FILTER('Append1','Append1'[Portfolio] in DISTINCT('Table 2'[Portfolio])))),[measure]),'Table 2'[ReplaceToZero])

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Oopa]

@v-kelly-msft , thank you for your response. I can see correct results from your powerBI. Only minor is that now we are using forecast and report date from Append1 table and not from dim_report_date or dim_forecast_date. Any solution to do that? Otherwise this might cause problems if we want to see other data which is joined also to these dim_report_date and dim_forecast_date tables.

[parry2k]

@Oopa share raw data, you need to have date dimension in your model to make it all work.

[Oopa]

@parry2k , Can I somehow attach powerbi file here?

[parry2k]

@Oopa share using one drive / google drive.

[Oopa]

@parry2k , Example data is quite simple:

Fact_dyn:

Report_Date	Forecast_Date	Amount	Portfolio
30.4.2020	30.4.2020	100	A
30.4.2020	30.4.2020	150	B
30.4.2020	30.4.2020	200	C
30.4.2020	1.5.2020	150	A
30.4.2020	1.5.2020	200	B
30.4.2020	1.5.2020	250	C
30.4.2020	2.5.2020	200	A
30.4.2020	2.5.2020	250	B
30.4.2020	2.5.2020	300	C

Fact_stat:

Report_Date	Amount	Portfolio
30.4.2020	400	D
15.5.2020	200	D

Dataflow:

[amitchandak]

@Oopa , refer how to join two dates to same time dim and use them using userelation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...