Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Amount Total for date values between two date columns

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
madhavan2427
Frequent Visitor
5 years ago

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.