Need help with measure that gathers amounts from empty cells

[RichOB]

Hi, using the table below, what measure would gather the amounts based on there Not being an end date? I need to show in a graph that July and August has $200 each.

Client_ID	Start_Date	End_Date	Amount
1	01/04/2024	22/04/2024	$150
2	01/05/2024	05/05/2024	$100
3	01/06/2024	10/06/2024	$80
4	01/07/2024		$200
5	01/08/2024		$200

Thanks!

[audreygerred]

In your graph, can you use start date instead of end date? If not, perhaps create a column that will provide the end date if there is one and start date if there is not - then use that new column as the axis in your chart. Your measure (regardless if you use the start date, the new column, or the date from a date table connected to one of those two columns as your axis) is Amount Total = SUM ('YourTable'[Amount]).

[dharmendars007]

Hello @RichOB , 

Please try this below measure..

Sum Amounts with No End Date =
CALCULATE(
SUM(ClientData[Amount]),
ISBLANK(ClientData[End_Date]) || ClientData[End_Date] = BLANK())

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[RichOB]

Hi @dharmendars007 , unfortunately, this didn't work, it kept giving me a blank card as if it was not pulling through the amounts. I broke down the measure and it worked until: 

I'm not sure if you know why that would be. Alternatively, do you know if there's a measure to turn blank values into the current date? That could also work.

Thanks! 

[dharmendars007 · Accepted solution]

Hello @RichOB , 

To turn Blanks to crrent date you just pass a condition if(isblank(column), Today())

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN