Amount Total for date values between two date columns

[Anonymous]

How do I calculated the total of amounts invoiced between two date columns.

Example:I have three columns - Date Invoiced, Date Paid and Amount. I want to create a column or measure that shows the aggregated amount of all invoices for invoice dates between the date invoiced and date paid.

So for the first row, the value for this column/measure should be the sum of amount for all invoices that have an invoice date between Invoice Date(6/30/2021) and Date Paid(7/2/2021). So if invoice date and date paid are the same for two records, then this value should be the same for the two records.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Measure = 
VAR _curinvdate =
    SELECTEDVALUE ( 'Table'[Invoice date] )
VAR _curpaiddate =
    SELECTEDVALUE ( 'Table'[Date paid] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount(USD)] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Invoice date] >= _curinvdate
                && 'Table'[Date paid] <= _curpaiddate
        )
    )

If the above one is not what you want, please provide some sample data with Text format and your expected result with calculation logic and special examples. Thank you.

Best Regards

[madhavan2427]

Hello @Anonymous , 

As much I understand your problem I am giving you a helping hand, 

You have to create a date table as DimDate and establish a relationship with the existing table first, then
Create a column as:

= Calculate(sum(Amont), DatesBetween(DimDate[Date], (invoice Date), (Date paid)))

Hope this will help you.

#Keep Learning #Keep Helping.

[amitchandak]

@Anonymous , expected output is not clear.

But you can have a common date table. Join Date Invoiced, Date Paid with that. And one join will inactive and then you can use userelationship in a measure to activate the join

refer example here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970