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 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.

 

 

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

1 Reply

  • 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