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 Date and CloseDate. Also I am having a dimension date table which has a relation to the date field of Offer table. Unfortunately, we are not writing offers every day (especially not on saturdays and sundays :-)) so it would be great if I could use the date field of my date table to see the amount of open offers for every day (irrespective of if an offer was created that day or not).

 

I tried many different things but I am not able to figure this out on my own.

 

Any help would be great.

 

Thx Christian

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.

7 Replies

  • 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!

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

    Hi hnguy71,

     

    thanks that you plan on helping me.

     

    Here you find a very simplified dataset, however, I hope it becomes more clear what I need.

     

     

    Again, thank you very much!

     

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi _chris_ 

       

      Please create the following measure in your model. 

      Open Amount = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    ALL ( 'Table' ),
    'Table'[Date] <= MAX ( 'Date'[Date] ),
    'Table'[CloseDate] > MAX ( 'Date'[Date] )
) + 0

       

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

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

        Thank you very much, unfortunately I am getting an error:

         

         

         

         A function of type 'MAX' has been used in a TRUE/FALSE expression which serves as a Table filter expression. This is not allowed.

         

        One more information here: I am not having a relationship between both tables. Is this correct?