I was able to get your assistance to solve an issue with my model two weeks ago. This is my second turn. Presently, I am using two date tables in my model. Initially, thought this was the easy and probably the best way to set up the model to get the result I want. However, I am running into issues when I am trying to bringing other measures into the picture. Other measures use the primary date table.
Anyways, here is the description of the business process. Attached the PBIX files, the question relates to the “changes future” tab. The table shows the output that i am looking to achieve using a single date table.
a) The sales table has two date columns - reading and revenue date. Reading date refers to the date the files was generated and the revenue date refers to the date the revenue will be recorded.
b) The current table shows the rooms sales recorded at each interval in the past for a future date. This is essentially showing the progression of rooms sales from past dates for the revenue dates in the future. Example - assuming today’s date is 6/5/2020, the record shows that we have 14 rooms sold for the revenue date of 7/1/2020 and furthermore, 3 days ago ( 6/5/2020 - 3 days = 6/2/2020), the company had recorded 15 rooms. In addition, 21 days ago for the revenue date of 7/1/2020, the company had sold 23 rooms.
How can I replicate these measures using the primary date table. Is the second date table a must in this model? I did try “use relationship” in the calculate function, but I could not replicate the same result. Revenue.pbix (1.2 MB)
Thanks in advance