The issue at hand is a simple model, with that said, I have been struggling to resolve the dax calculation to generate the desired output.
The model includes;
a) Sales_Revenue Fact sheet
This table include revenue data taken at each day leading up to the the month the revenue is actualized. Each new file ( new read date) would have revenue data for the present day + 365.
A few definition
Read date : The system generated date for the file
Revenue -Date / Revenue - Month - The date or the month when the revenue on the books will be actualized.
b) Calendar Table
The two connections are inactive to the calendar table.
I am looking to generate the following visualization.
The two filters on top of the page - Revenue month and Revenue week # . This is basically the period in which the revenue on the books will be actualized. I prefer to include both as it gives more flexibility for granularity.
The bottom row - For a given revenue-month, illustrates running on the books revenues by read - week ( read date). For an example, if the revenue month in focus is march 2019, then the chart should show the progressive reading of the revenue starting from the prior weeks for mar 2019.
The top row - this shows the variance from the prior week. As an example, for the revenue month of march 2021, revenue was recorded to be at $ 76k at the end of week 2 ( week # in the year) , however the revenue was recorded to be 71k at end of week 1 so the variance of 6k should be reflected on the top raw for week 2.
PBIX file is attached. I greatly appreciate any help.
Revenue1.pbix (1.2 MB)