Calculate an aggregated sum with 2 dynamic ref dates (calculated column)

[CJMolin]

Hello!
I am a new member to this forum but been browsing the topics quite frequently since i started to use the DAX language. Unfortunately I’ve now got stuck and would love to reach out to you and see if there are any solution(s) to my problem.

I am having a table with a datestamp which I want to use dynamically in order to calculate how many contracts that has been sent out during this dynamic period of time (as a calculated column, not a measure).
Eg. I want to sum how many contracts has been sent out between StartDt and EndDt relative to the Date shown in Table 1.


This is the data I have
Table 1

Date	Contract (1/0)	CalculatedColumn
2017-05-01	1
2017-05-01	1
2017-05-07	0
2017-05-09	1

Table 2

Date (key)	StartDt (-120 days from Date)	EndDt (-30 days from Date)
2017-05-01	2017-01-01	2017-04-01
2017-05-02	2017-01-02	2017-04-02
2017-05-03	2017-01-03	2017-04-03
etc..	..	..

The DAX-expression used to far is:

=CALCULATE(SUM(DecisionFilter[DocsCustomer (1/0)]);FILTER(ALL(DecisionFilter);DecisionFilter[DateDocsCustomer]<=MAX(DecisionFilter[DateDocsCustomer]))) which only generated how many Contracts that has been sent out in total..

Please help me!
Thank you very much in advance,
CJ

[CJMolin · Accepted solution]

If this would help anyone the answer to my request is the following

=CALCULATE(SUM(DecisionFilter[DocsCustomer (1/0)]);FILTER(DecisionFilter;DecisionFilter[DateAnswerLender]>=EARLIER(DecisionFilter[DateAnswerLender])-120&&DecisionFilter[DateAnswerLender]<=EARLIER(DecisionFilter[DateAnswerLender])-30&&DecisionFilter[ProductId]=EARLIER(DecisionFilter[ProductId])))

[CJMolin]

Hello!
I am a new member to this forum but been browsing the topics quite frequently since i started to use the DAX language. Unfortunately I’ve now got stuck and would love to reach out to you and see if there are any solution(s) to my problem.

I am having a table with a datestamp which I want to use dynamically in order to calculate how many contracts that has been sent out during this dynamic period of time (as a calculated column, not a measure).
Eg. I want to sum how many contracts has been sent out between StartDt and EndDt relative to the Date shown in Table 1.


This is the data I have
Table 1

Date	Contract (1/0)	CalculatedColumn
2017-05-01	1
2017-05-01	1
2017-05-07	0
2017-05-09	1

Table 2

Date (key)	StartDt (-120 days from Date)	EndDt (-30 days from Date)
2017-05-01	2017-01-01	2017-04-01
2017-05-02	2017-01-02	2017-04-02
2017-05-03	2017-01-03	2017-04-03
etc..	..	..

The DAX-expression used to far is:

=CALCULATE(SUM(DecisionFilter[DocsCustomer (1/0)]);FILTER(ALL(DecisionFilter);DecisionFilter[DateDocsCustomer]<=MAX(DecisionFilter[DateDocsCustomer]))) which only generated how many Contracts that has been sent out in total..

Please help me!
Thank you very much in advance,
CJ

[v-huizhn-msft]

Hi @CJMolin,

Please try @Greg_Deckler posted formula. If you have not resolved your issue, you'd better post the sample table. For the given tables, I am unable to reproduce it.

Thanks,
Angelia

[Greg_Deckler]

Something like this:

Column = CALCULATE(COUNT(Orders[OrderNo]),FILTER(RELATEDTABLE(Orders),Orders[StartDate]<='Calendar'[Date] && Orders[EndDate]>='Calendar'[Date]))

[CJMolin]

Hello @Greg_Deckler and @v-huizhn-msft

Thank you very much for your effors. Unfortunately the given expression only gives me what I already have accomplished to get, and that is not a cumulative sum during the time frame wanted. Maybe I was unclear in my request. I therefor attach a link with an excel-sheet (with the table both in excel and powerpivot) http://www.filedropper.com/examplehelpdax I also attach a simple picture with the explanation.

If you want me to provide you the examples in some other way, please let me know.

I would still really much appreciate your help, 

Thank you very much in advance,

C