Calculate total sum between date from one table and today's date

[sherwood]

Likely an easy one for a Power BI Guru, but my calculation is not returning the correct result.

I have data like the below table, with Device Returned Date coming from a separate data source. 



The Cost Since Return column (that I'm trying to write a calculation for) is shown above as my desired result.

I'm trying to write a calculation that can give me totals like:

I'm not sure what I'm doing wrong but my calculation is showing incorrect figures between Service Cost and Cost Since Return, giving me bogus totals. 

Any help greatly appreciated. 

[v-zhangti · Accepted solution]

Hi, @sherwood 

You can try the following methods.

Column = 
IF ( 'Table'[Device Returned Date] <> BLANK (),
    CALCULATE ( SUM ( 'Table'[Service Cost] ),
        FILTER ( 'Table',
            [Billing Month] >= [Device Returned Date]
                && [Billing Month] <= TODAY ()
                && [Device ID] = EARLIER ( 'Table'[Device ID] )
        )
    ),
    BLANK ()
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[sherwood]

That's great, Charlotte! Many thanks.

[FreemanZ]

hi @sherwood 

could you elaborate how to get the values in Cost Since Return column?

[andhiii079845]

Please share a good data example, no screenshot. Are this columns or measures? give us some "light" 🙂

[sherwood]

Here's the calculation I've been trying, but I don't think it's correct. I'm getting unexpected figures from it.


Cost Since Return = 
IF( ' Table1' [Device Returned Date] <> BLANK,

CALCULATE(
SUM( 'Table1' [Service Cost]),
DATESBETWEEN(
'Table1' [Billing Month],
'Table1' [Device Returned Date],
TODAY (),
))
BLANK,
)

[andhiii079845]

Than make a good example. We need the uderlaying data structure, make it easier to help. 

[andhiii079845]

Can you share please all involved tables with the example. Please use the internal table function, no screenshot. 

I do not really understand you disired result column, you write 50,50, show it be 50,100 (because of 2 service cost month?

[sherwood]

Hi, this is representative data as I'm unable to share the original. 

The desired result is to include the Service Cost only in months after the Device Return Date.