Time Intelligence Date Question

Hello Anonymous ,

You need to take help of a calendar table

Once added in the model, create the relationship between the calendar table date, and Claim Date & Claim Due Date

Then you can create following measures for calculating the total revenue on claim date and due date

Total Claim Revenue = 
CALCULATE(
    SUM(dtTable[Claim Revenue]),
    USERELATIONSHIP(dtCalendar[Date],dtTable[Claim Date])
)

Total Due Date Revenue = 
CALCULATE(
    SUM(dtTable[Due Date Revenue]),
    USERELATIONSHIP(dtCalendar[Date],dtTable[Claim Due Date])
)

You will get the following results:

Alternatively, you can use TREATAS function and skip the Relationship creation part suggested above (Calendar table is required though):

Total Revenue = 
CALCULATE(
    SUM(dtTable[Claim Revenue]),
    TREATAS(VALUES(dtCalendar[Date]),dtTable[Claim Date])
)

Total Due Date Revenue = 
CALCULATE(
    SUM(dtTable[Due Date Revenue]),
    TREATAS(VALUES(dtCalendar[Date]),dtTable[Claim Due Date])
)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn