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!