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.

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?

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    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.

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

      Hi jingzhang,

       

      now it works perfectly. Thank you very much!

       

      The "old" measure I got running with a small file still yesterday evening (not my "big" model). However, I wanted to do something like IF(ISBLANK(Table[CloseDate]), DATE(2100, 12, 31), Table[CloseDate])) as offers which are not having a close date also should be included. This did not work with the first measure. But now also this works perfectly fine.

       

      Thx again!

       

      Christian