Cumulative Running total Dates missing

[Jayesh]

Hi Team,

i have one defect table and one Date dim table. The defect table have 2 dates creation date and resolution date. I have created 2 relationship between date dim and defect table with creation date and date dim as active relationship. i am trying to achieve the running total using date dim date and the other two dates, but not getting expected results, please refere the screenshot and help me here.

[Anonymous · Accepted solution]

Hi  @Jayesh ,

Here are the steps you can follow：

1. Create measure.

Total Defects Measure =
SUMX(
    FILTER(ALL('defect table'),
    'defect table'[creation date]<=MAX('Date dim table'[creation date])),[Total Defects])

Total Open Defects Measure =
SUMX(
    FILTER(ALL('defect table'),
    'defect table'[creation date]<=MAX('Date dim table'[creation date])),[Total Open Defects])

Total Resolved Defects Measure =
var _table1=
ADDCOLUMNS(
    'defect table',"Date",
    MAXX(
        FILTER(ALL('defect table'),
       'defect table'[creation date]<=EARLIER('defect table'[resolution date])),
        [creation date]))
var _table2=
ADDCOLUMNS(
    _table1,"Value",SUMX(FILTER(_table1,[Date]=EARLIER([Date])),[Total Resolved Defects]))
return
SUMX(
    FILTER(_table2,[Date]<=MAX('Date dim table'[creation date])),[Value])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly