Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
simon139
Frequent Visitor

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.

simon139_0-1677072011316.png

 



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



0 REPLIES 0

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors