Sum Distinct values by Month

[RogerSteinberg]

Hi,

I am trying to sum the cost column by StartMonth. The issue is that my cost can be duplicated for a Product if there are multiple End Dates. For example, in row 2 and 3, the cost is just 10$ NOT 20$.

Start                End               Cost   Product

2020-01-01		5	A
2020-01-01	2020-02-01	10	B
2020-01-01	2020-03-01	10	B
2020-01-01	2020-02-01	15	C
2020-01-01	2020-03-01	15	C
2020-02-01	2020-05-01	30	D
2020-02-01	2020-06-01	30	D
2020-02-01	2020-05-01	50	E
2020-02-01	2020-06-01	50	E
2020-02-01		100	F

The final output should look something like this:

Start	Cost
1/1/2020	30
2/1/2020	180

For January: Product A (5$), Product B (10$), Product C (15$)

For February: Product D (30$), Product E (50$), Product F (100$)

I tried this formula and almost works but it's not able to sum the rows with Blank End Dates. How can I make sure it takes them into account?:

Cost By YearMonth = 
CALCULATE(
    SUM('Fact'[Cost]),
    ALLEXCEPT(
        'Fact',
        'Fact'[Start],
        'Fact'[End]
    ))

Note: I need a calculated column NOT a measure

[amitchandak]

@RogerSteinberg ,

Try sumx(summarize(Table, Table[Start],Table[Product],"_1",max(Table[Cost])),[_1])

[RogerSteinberg]

I need a calculated column

[mahoneypat · Accepted solution]

Here is a column expression that works, although you should consider doing this either as a measure or in the query editor with a Group By step.

Cost by YearMonth =
CALCULATE (
SUMX ( VALUES ( 'Fact'[Product] ), CALCULATE ( AVERAGE ( 'Fact'[Cost] ) ) ),
ALLEXCEPT ( 'Fact', 'Fact'[Start] )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat