Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
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.
@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.
@Oopa share raw data, you need to have date dimension in your model to make it all work.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Oopa share using one drive / google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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:
@Oopa , refer how to join two dates to same time dim and use them using userelation
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |