Forum Discussion
Helper IICalculate an aggregated sum with 2 dynamic ref dates (calculated column)
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
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])))
5 Replies
CJMolinHello!
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 1Date Contract (1/0) CalculatedColumn 2017-05-01 1 2017-05-01 1 2017-05-07 0 2017-05-09 1
Table 2Date (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- Greg_Deckler
Community Champion
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
v-huizhn-msftMicrosoft Employee
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
- CJMolin
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])))
