Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two tables, one is a forecast amount by month, and one is the transaction data by day. The forecast by month is stored on the last day of the month (in this example 5/31/2020). The transaction data comes in daily. I would like to get a measure that will show that monthly total as a daily rate, so I can plot along side my transaction data to compare daily forecast vs. actual (which will have values for each day of the month). I am having trouble with this measure.
Forecast Table
Date | Forecast |
5/31/2020 | 14000 |
Transaction Table
Date | Amount |
5/1/2020 | 2000 |
5/2/2020 | 100 |
5/3/2020 | 500 |
Solved! Go to Solution.
Hi @cabbey ,
Based on your description, you can create calculated columns in two tables.
year_month = FORMAT('Table (2)'[Date],"YYYY-MM")
year_month = FORMAT('Table'[Date],"YYYY-MM")
Create a one to many relationship for two tables based on the created calculated columns.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cabbey ,
Based on your description, you can create calculated columns in two tables.
year_month = FORMAT('Table (2)'[Date],"YYYY-MM")
year_month = FORMAT('Table'[Date],"YYYY-MM")
Create a one to many relationship for two tables based on the created calculated columns.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@cabbey As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...
Set relationship this date table with your both the tables, on visual, take a month from date dimension and the sum of values from transaction and forecast, and everything will flow together.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.