Forum Discussion

simon139's avatar
simon139
Frequent Visitor
3 years ago

Create a calculate table between 2 dates and sum values

Hey, 

I have : 

Calendar table (with year, month, quarter etc.)  (calendar_v) and Contracts table which is filtered by Calendar_v (date filters -> start_date). My Contract table have monthly amount which should be calculated to a Total between start_date and end_date (but still should be filtered by calendar). 

So let's say for contractor issue id 362030 , I have monthly amount of 1000 , start_date is 01.01.2023 and end_date is 31.01.2024. 
So total value of contract will be 12 000 (as it's full 12 months), but if I filter with calendar to 2023 it should only show the part for 2023 which is 11 months * 1000 = 11 000.

 



I would also be able to put this on time series and split it into Year-Month from the calendar.

Is there a way to create a DAX formula without any additional helping tables? (which would kind of calculate an extra table with every month between start date and end_date and create). I want to simplify my model as possible. 

Here is sample data: 

https://drive.google.com/drive/folders/1rSqX30l9CmzlYCLKA5lyvRRTJNzZHatt?usp=share_link



No RepliesBe the first to reply