Calculate the sum of some values with a conditional date

[Anonymous]

Hi, I have a table like this:

Client Name	Contract start	Contract end	Authorized Users
Customer B	30/06/2020	27/12/2020	5
Customer B	15/06/2021	29/06/2022	10
Customer A	01/10/2021	30/12/2021	10
Customer B	30/11/2021	29/11/2022	10
Customer A	02/01/2022	01/01/2023	10
Customer B	01/05/2022	30/04/2023	40

And a date reference table(calendar), I would like to know, for a certain period of time in the slicer, what is the number of authorized users, and being able to slice it also by client name, I have done this formula but it doesn't work properly.

Total Contracted Users =

VAR CurrentDate = MAX('calendar'[Date])

VAR ContractedUsers =

CALCULATE(

SUM('Contracts Master Table'[Authorized Users]),

ALL('calendar'),

'calendar'[Date]<=CurrentDate,

ISBLANK('Contracts Master Table'[Contract end])

|| 'Contracts Master Table'[Contract end]>=CurrentDate

)

RETURN

ContractedUsers

Any help is welcome!

[Anonymous · Accepted solution]

Thank you @speedramps it worked, just I had to use MAX instead of SELECTEDVALUE for the first variable because it was not working. My final formula is 

No. Contracted Users = 
VAR CurrentDate = MAX('calendar'[Date])
VAR ContractedUsers =
FILTER('Contracts Master Table',
'Contracts Master Table'[Contract start]<= CurrentDate &&
'Contracts Master Table'[Contract end] >= CurrentDate)
RETURN
CALCULATE(
    SUM('Contracts Master Table'[Authorized Users]),
    ContractedUsers
) 

[speedramps]

Try this solution with your correct table / field names.

I have added comments so yoy can learm dax ....

Create a detached calendar table (with no relationships)

Create this measure ...

Users for pickdate =
// get slicer pick date

VAR mypickdate = SELECTEDVALUE(Calendar[date])

// create subset of master records that are valid for the pickdate
VAR mysubset  =
FILTER(master,
master[startdate] <= mypickdate &&
master[enddate] >= mypickdate)

// return the sum of the users for the subset

RETURN
CALCULATE(

SUM( master[users]),
mysubset 
)

Create report with

dateslicer = Calendar[date]) with single selection turned on

client slicer = master[client]  with mutli selection turned on

A table visual with

• master[client] 
• [Users for pickdate]

This will work !

Please click thumbs up and accept as solution.

One problem per ticket please.

If you need to expan your problem then click accept as solution and raise a new ticket.

You willl get a quicker response and each solver gets the kudos they deserve. Thank you  😀