Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My model has two inactive relatioships to the date table to two dates columns in the fact table. This is done to help with few of the measures that I have built in the model. The two dates columns are reading date and revenue date. Reading date aka recorded date or report generated date. The report is generated on a daily basis. On the otherhand, revenue date is the actual financial date where room sales and revenues are attributed to. I am having diffIculties in calculating revenues at the start of the month and end of the month for past and future months in the calendar.
logic - if the reading date = startof the month ( eg: 1/1/2020 ), then calculate revenue for the respective months ( revenue date) in the calendar. All future revenue numbers will get updated as we get new batch of files.
Example - on the 1/1/ 2020, the revenue recorded for March was 1 mn, as with time and we get new files, revenue for the month of March would probably be at 1.5mn on the 1st of Feb. Essentialy, all future month's revenue position is a snapshot at a given start of the month. and all the past months will remain at their actualized numbers.
Month | Start of the month | End of the month | Change |
Jan | |||
Feb | |||
Mar | |||
Apr | |||
May | |||
June | |||
July | |||
Aug | |||
Sep | |||
Oct | |||
Nov | |||
Dec |
Thanks in advance
Iqbal
Hello @iqbal077 ,
The requirement is not clear from your description. What is the logic of calculating revenue for March based on revenue of January?
Can you share a sample data file with expected results?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |