Forum Discussion

Jayesh's avatar
Jayesh
Regular Visitor
1 year ago
Solved

Cumulative Running total Dates missing

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 crea...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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