Forum Discussion

Oopa's avatar
Oopa
Frequent Visitor
6 years ago

How to keep measure's value same over time

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). ...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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's avatar
Oopa
Frequent Visitor
6 years ago

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.