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.
Hi 🙂
I'm looking for a solution to display actuals and forecast values in one measure/column. I then want to display this measure/column in a linechart (x axis = Date; y= Value).
All I have are the actual values with a timestamp, what I want is to calculate an average value multiplied by an parameter (which will be a slicer, so the viewer can adjust the increase)
Value | Date | Source |
10 | 2019-09-02 | Actual |
20 | 2019-09-03 | Actual |
17 | 2019-09-04 | Forecast |
17 | 2019-09-04 | Forecast |
... | ... | ... |
17 | 2019-12-31 | Forecast |
What I have:
Value | Date |
10 | 2019-09-02 |
20 | 2019-09-03 |
My first approach was to calculate a tally table in m query to get all dates for the current year
= List.Dates(#date(Date.Year(DateTime.LocalNow()), 1, 1), 365, #duration(1, 0, 0, 0))
I then join both tables to get this table:
Value | Date | Date_joined |
10 | 2019-09-02 | 2019-09-02 |
20 | 2019-09-03 | 2019-09-03 |
2019-09-04 | ||
... | ||
2019-12-31 |
With DAX I created a calculated column to fill in the blanks with the average of [value] * the value from the slicer (Adjustment Value). It does work with my dummy data to fill in the average amount but the '*Adjustment[Adjustment Value])' section is beeing ignored.
IF(DummyData[Date]=DummyData[Date_joined];DummyData[Value];(DummyData[Average]*Adjustment[Adjustment Value]))
Value | Date | Date_joined | Combined |
10 | 2019-09-02 | 2019-09-02 | 10 |
20 | 2019-09-03 | 2019-09-03 | 20 |
2019-09-04 | 15 | ||
... | 15 | ||
2019-12-31 | 15 |
As always, I think there possibly is an easy solution I'm not aware of.
Thanks in advance for any guidance 🙂
You can download the file here: Link
Sources I tried but couldn't get to work:
https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/
https://community.powerbi.com/t5/Desktop/Revenue-forecast-from-actual-sales/m-p/280437#M124944
Solved! Go to Solution.
I think it's cozed by you didn't set the relationship between DummyData table with Adjustment table. The adjustedaverage value could not be defined correctly.
When I rebuild the adjustment table with adding "Person" column, and manage the relationship. the [Combined] column show directly.
Would you mind share me your dummy pbix? that would be easier for us to search in depth.
Hi @v-diye-msft ,
I uploaded the file; also when creating this dummy environment filling the blanks with an average value works. So there seems to be an issue with my real data. But the multiplication with the what if parameter does not work. Maybe you'll have an idea whats wrong.
also it would be interesting to know if there is an easier solution for my problem.
all the best and thank you!
I think it's cozed by you didn't set the relationship between DummyData table with Adjustment table. The adjustedaverage value could not be defined correctly.
When I rebuild the adjustment table with adding "Person" column, and manage the relationship. the [Combined] column show directly.
Hi @v-diye-msft
thank you very much for your reply and the attached file. I'll take a look into it today.
best
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |