Forum Discussion

BBConsultancy's avatar
BBConsultancy
Icon for Helper I rankHelper I
1 year ago

DAX measure to replace large calculated table

Hi,   I have a table with contact person history. Each row represents a change in the status of my contact person. For example it shows that person a got status 'Approved' on 01-01-2025, on another...
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

BBConsultancy Create a measure to calculate the number of contact persons per status on a given date:

ContactPersonsPerStatus =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
COUNTROWS(ContactpersonHistory),
FILTER(
ContactpersonHistory,
ContactpersonHistory[CreatedDate] <= SelectedDate &&
(ISBLANK(ContactpersonHistory[EndDate]) || ContactpersonHistory[EndDate] >= SelectedDate)
)
)

 

Create a measure to calculate the number of contact persons per status over time:

DAX
ContactPersonsPerStatusOverTime =
CALCULATE(
[ContactPersonsPerStatus],
DATESBETWEEN(
'Calendar'[Date],
MIN('Calendar'[Date]),
MAX('Calendar'[Date])
)
)

  • BBConsultancy's avatar
    BBConsultancy
    Icon for Helper I rankHelper I
    1 year ago

    Thanks for the fast reply. Unfortunately it does not work.

    Left is your solution, and right is the expected result. It looks like left isn't accumulating over time.