Calculate Sum of Points based on 2 Date Columns

[jsdgooch]

Hi there!

Need help on calculating the Points of each member based on their Aniversary Date.  I am trying to figure out but I don't have advance knowledge in DAX.  I have created a calculated fields "From" and "To" based on the "Anniversary Date" and I wanted to use those to query the total points.

I have two tables, Members & Transactions and is linked via Member ID.

Members:

Member ID, Anniversary, From, To

1234, 8/20/2024, 8/20/2024, 3/31/2025

4567, 7/24/2023, 7/24/2024, 3/31/2025

Transactions:

Member ID, Date, Points

1234, 8/30/2024, 100

1234, 12/31/2024, 100

1234, 3/1/2025, 100

5678, 6/31/2024, 100

5678, 9/30/2024, 100

5678, 10/31/2024, 100

5678, 2/28/2025, 100

My desired result would be:

Members:

Member ID, Anniversary, From, To, Points

1234, 8/20/2024, 8/20/2024, 3/31/2025, 300

4567, 7/24/2023, 7/24/2024, 3/31/2025, 300

How can I achieve this?

Many thanks!

Regards,

Jorrel D.

[jsdgooch]

OMG!

That does it!  The query is rather simple... am trying to use DATESBETWEEN but to no avail.

Many thanks for this!

Kudos!

Regards,

Jorrel D.

[Greg_Deckler]

@jsdgooch Just say no to DAX time "intelligence" functions. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Greg_Deckler · Accepted solution]

@jsdgooch 

Points Measure = 
  VAR __From = MAX( 'Members'[From] )
  VAR __To = MAX( 'Members'[To] )
  VAR __Table = FILTER( 'Transactions', [Date] <= __To && [Date] >= __From )
  VAR __Result = SUMX( __Table, [Points] )
RETURN
  __Result