Revenue Pace and Running Total - Dax calculation

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.

To start,

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)

Hi @iqbal, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the screenshot of the data model, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hello @iqbal. We have a thread with some recommendations from other Power BI users which could help. Here’s the link where you can go to view it - Add a filter for product in a running total calculation, 60 Day Running Totals with Non Contigous Dates and Calculating Running Totals in Power BI Using DAX . Hope this helps.

Hi @iqbal, we’ve noticed that no response has been received from you since the 13th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Thanks for the note. I was able to partially solve the issue by using group by function however I am still struggling to find the Dax formula to retrieve the total associated to a specific date in the week. Its not the total of each day aggregated at the end of the week. So having a hard time in linking the end of the date and tie that to the date in the fact table and visually present the total with the week label.

Thanks in advance
Iqbal

As an example,

In a Sun thru Sat week definition, I want to retrieve the total in the last date (read date) of the week file. In the current week, the date would be 23rd of Jan. I think I made a mistake by calling the post as the running total. How do I link the end of the week ( every Sat of the week) in the calendar table and tie that to the read date in the fact table?

Further to my note, I want to create the visuals to show the totals for 1/5/2019( week 1), 1/12/2019 ( week 2), 1/19/2019 ( week 3). I hope this gives more clarity of the solution. The issue is how do I link the end of the week to retrieve the total?