topic Re: Assigning Revenue and Profit daily between start and end dates in DAX Commands and Tips

Assigning Revenue and Profit daily between start and end dates

BciBuilder — Wed, 05 Feb 2025 16:15:26 GMT

I have been trying to create a DAX measure to assign my project Revenue and Profit between a start and end date. My end goal is to be able to look at the values of each project by month to compare to our company budget (from another table). I have created calculated columns for work days, Revenue by Work Day and Profit by Work Day. Those are in my Outlook table and I have been tying them to a date table called DateB.

Here is a rough matrix table with the end goal view. Most of the DAX formulas I've tried to assign have resulted in a lump sum dollar amount to either the start or end date.

Thanks in advance for the help!

Re: Assigning Revenue and Profit daily between start and end dates

Anonymous — Thu, 06 Feb 2025 02:25:15 GMT

Hi BciBuilder ,

Do you want to calculate the total number of Revenue and GM and their Budget for each month? If there is a relationship between your Date table, Outlook table and Budget table, such as Date[Date]-->Outlook[Date](One to Many), then when you use the Date[Date] in the matrix, the matrix will usually calculate the total for each month by itself.

I don't quite understand your question, could you please provide sample data for each table and the expected output? In addition, how much is the daily allocation?

Best regards,

Mengmeng Li

Re: Assigning Revenue and Profit daily between start and end dates

sangeethray_92 — Thu, 06 Feb 2025 08:09:19 GMT

Do you have a dedicated Date Table in your data model. If you don't have one, consider creating it.
In your Budget Table, check whether the dates represent only the start of month (e.g., 2024-01-01 for January, 2024-02-01 for February, etc.).
If your budget dates are always the first day of the month, you need to add a new calculated column in your Date Table that represents the start of each month.
Create a relationship between the StartOfMonth column in your Date Table and the Date column in your Budget Table.

Try to follow the above steps.

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Re: Assigning Revenue and Profit daily between start and end dates

BciBuilder — Thu, 06 Feb 2025 15:32:30 GMT

Here is a picture of the matrix chart I'm trying to populate. The Budget come from a separate table that have a monthly breakdown. With the actual, i'm struggling to get it to spread my Rev Per Work Day and GM per work day to each work day between my start and end. It seems to be grabbing it by week or other measure's I've tried to create either put the dollars at the start or end date. Really want the rev per day in the table as the start or end date shift it moves the dollars with them. For example. If a project start on 2/27/25 this year and went through 3/28/25. I'd want two days of that daily revenue showing up in February and the rest to populate in March.