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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MiKeZZa24
Frequent Visitor

Calculate correct numbers based on table with start- and enddates

Hi all I have a table with 2 types of capacity in it and for every record a startdate and enddate. Some dummy data is here:
 Schermafbeelding 2023-04-13 145959.png

As you can see there is capacity for a costcenter or capacity for a group. A group is a lower level than a costcenter. This all is based on a very simple starschema.

Schermafbeelding 2023-04-13 152153.png

 

Now I'm creating measures to show the correct amount of capacity for groups and costcenters. My measures are defined as follows:

 

CC Capacity = 

VAR CurDate = SELECTEDVALUE('Date'[Date])
VAR x = SUMX('Date', MAXX(FILTER(Capacity, NOT(ISBLANK(Capacity[CapacityCC])) && CurDate < Capacity[Enddate] && CurDate >= Capacity[Startdate]), SUM(Capacity[CapacityCC])))

RETURN x

and

Group Capacity = 

VAR CurDate = SELECTEDVALUE('Date'[Date])
VAR x = SUMX('Date', MAXX(FILTER(Capacity, NOT(ISBLANK(Capacity[CapacityGroup])) && CurDate < Capacity[Enddate] && CurDate >= Capacity[Startdate]), SUM(Capacity[CapacityGroup])))

RETURN x

 

But what's wrong here is the output 🤣

 

I have some examples.

 

Problem 1

Example 1 is selecting 1 specific data (i.e. 2 february 2021) which is giving me the incorrect amount because it's summing all the records that apply, not only the one that should be active based on there start- and enddate.

Schermafbeelding 2023-04-13 151900.png

38 is the result of 18 + 20 while it should be only 18. And 62 is the sum of 30 and 32 instead of just 30.

 

Problem 2

Problem 2 is when you don't select 1 specific date but a month it's not showing anything. While I should expect the last empty value for that specific month/group/cc.

 

Is there a dax-guru here to help me out?

 

File with my example data and all relevant dimensions can be found here.

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can use

CC Capacity copy = 
VAR CurDate = MAX( 'Date'[Date] )
VAR x =
	CALCULATE(
		SUM( 'Capacity'[CapacityCC] ),
		NOT ISBLANK( 'Capacity'[CapacityCC] )
			&& CurDate < 'Capacity'[Enddate]
			&& CurDate >= 'Capacity'[Startdate]
	)
RETURN
	x

Group Capacity copy = 
VAR CurDate = MAX( 'Date'[Date] )
VAR x =
	CALCULATE(
		SUM( 'Capacity'[CapacityGroup] ),
		NOT ISBLANK( 'Capacity'[CapacityGroup] )
			&& CurDate < 'Capacity'[Enddate]
			&& CurDate >= 'Capacity'[Startdate]
	)
RETURN
	x

View solution in original post

2 REPLIES 2
MiKeZZa24
Frequent Visitor

And if I have an additional wish; I also want to see the numbers for a month where a record stops as well? So by example the line with enddate 25-12-2023 must be visible in december 2023 as well. I've tried some things but for the situation where a new line follows up with start 26-12-2023 I go double...

johnt75
Super User
Super User

You can use

CC Capacity copy = 
VAR CurDate = MAX( 'Date'[Date] )
VAR x =
	CALCULATE(
		SUM( 'Capacity'[CapacityCC] ),
		NOT ISBLANK( 'Capacity'[CapacityCC] )
			&& CurDate < 'Capacity'[Enddate]
			&& CurDate >= 'Capacity'[Startdate]
	)
RETURN
	x

Group Capacity copy = 
VAR CurDate = MAX( 'Date'[Date] )
VAR x =
	CALCULATE(
		SUM( 'Capacity'[CapacityGroup] ),
		NOT ISBLANK( 'Capacity'[CapacityGroup] )
			&& CurDate < 'Capacity'[Enddate]
			&& CurDate >= 'Capacity'[Startdate]
	)
RETURN
	x

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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