Forum Discussion

_chris_'s avatar
_chris_
Icon for Helper III rankHelper III
4 years ago
Solved

Running totals between to dates

Hi,   I have a fact table with offers:   OfferId Date CloseDate Amount   I want to have the running totals for all my offers. However, the amount of an offer should only be added between Dat...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi _chris_ 

     

    Try this measure instead. It doesn't care whether there is a relationship between both tables because it uses ALL to remove all filters in the context before comparing dates. 

    Open Amount = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Date'[Date] ) && 'Table'[CloseDate] > MAX ( 'Date'[Date] )
    ),
    'Table'[Amount]
) + 0

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
4 years ago

Hi _chris_ ,

 

Can you supply some sample data and expected output? This seems easy but with some data it would be better to understand your requirements a bit further.

_chris_'s avatar
_chris_
Icon for Helper III rankHelper III
4 years ago

Thanks hnguy71 that you plan on helping me 🙂

OfferIdDateCloseDateAmount
115.03.202218.03.2022100
217.03.202221.03.2022

1000

 

DateOpenAmount
… 
14.03.20220
15.03.2022100
16.03.2022100
17.03.20221100
18.03.20221000
19.03.20221000
20.03.20221000
21.03.20220
… 

 

This is very simplified my scenario but I hope it becomes clear what I need...

 

Again, thank you very much!