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). 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_DateForecast_DateAmountPortfolio
30.4.202030.4.2020100A
30.4.202030.4.2020150B
30.4.202030.4.2020200C
30.4.20201.5.2020150A
30.4.20201.5.2020200B
30.4.20201.5.2020250C
30.4.20202.5.2020200A
30.4.20202.5.2020250B
30.4.20202.5.2020300C

 

Fact_stat table:

Report_DateAmountPortfolio
30.4.2020400D
15.5.2020200D

Dataflow:

Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

  • 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.