Create Daily measure from monthly forecast

[cabbey]

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

[V-lianl-msft · Accepted 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.

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@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.⚡