Running totals between to dates

[_chris_]

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

[_chris_]

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]

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_]

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 · Accepted solution]

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_]

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

[hnguy71]

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_]

Thanks hnguy71 that you plan on helping me 🙂

OfferId	Date	CloseDate	Amount
1	15.03.2022	18.03.2022	100
2	17.03.2022	21.03.2022	1000

Date	OpenAmount
…
14.03.2022	0
15.03.2022	100
16.03.2022	100
17.03.2022	1100
18.03.2022	1000
19.03.2022	1000
20.03.2022	1000
21.03.2022	0
…

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

Again, thank you very much!