Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])))
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])))
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
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
Something like this:
Column = CALCULATE(COUNT(Orders[OrderNo]),FILTER(RELATEDTABLE(Orders),Orders[StartDate]<='Calendar'[Date] && Orders[EndDate]>='Calendar'[Date]))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |