Forum Discussion
Count by Date Measure
I created a date measure by pulling a date field based on a keyword search:
DOT Date = CALCULATE(MAX(LoanContractPostClosingConditionLogs[DateReceived]),SEARCH("Deed of Trust", LoanContractPostClosingConditionLogs[Description],1,0)>0)
So when I'm using this measure with the LoanNumber field, I'm getting something like this:
| LoanNumber | DOT Date |
| 001 | 7/9/2021 |
| 002 | 7/2/2021 |
| 003 | 7/2/2021 |
| 004 | 6/5/2021 |
| 005 | 6/5/2021 |
| 006 | 7/2/2021 |
If I want to take it a step further to count the number of loans for each DOT Date, so that it looks something like this:
| DOT Date | Number of Loans |
| 6/5/2021 | 2 |
| 7/2/2021 | 3 |
| 7/9/2021 | 1 |
How do I build a Number of Loans measure? Is it possible to relate this DOT Date field to my Date table so that if I create a date slicer it'll reference the DOT Date? Or I'd just have to build the slicer based on the DOT Date field itself?
Anonymous try this measure, assuming you have a calendar table in your model, and to visualize, use date from calendar table and the following measure:
Loan Count by DOT Date = VAR __LoansInDate = FILTER ( SUMMARIZE ( Data, Data[Loan] ), VAR __LoanDOTDate = [Loan DOT Date] VAR __LoanDOTDateInDate = FILTER ( 'Calendar', NOT ISBLANK ( __LoanDOTDate ) //can remove this condition if never going to be a blank Loan Max Date && 'Calendar'[Date] = __LoanDOTDate ) VAR __IsLoanInDate = NOT ISEMPTY ( __LoanDOTDateInDate ) RETURN __IsLoanInDate ) VAR Result = CALCULATE ( DISTINCTCOUNT ( Data[Loan] ), KEEPFILTERS ( __LoansInDate ) ) RETURN Result✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
5 Replies
Anonymous try this measure, assuming you have a calendar table in your model, and to visualize, use date from calendar table and the following measure:
Loan Count by DOT Date = VAR __LoansInDate = FILTER ( SUMMARIZE ( Data, Data[Loan] ), VAR __LoanDOTDate = [Loan DOT Date] VAR __LoanDOTDateInDate = FILTER ( 'Calendar', NOT ISBLANK ( __LoanDOTDate ) //can remove this condition if never going to be a blank Loan Max Date && 'Calendar'[Date] = __LoanDOTDate ) VAR __IsLoanInDate = NOT ISEMPTY ( __LoanDOTDateInDate ) RETURN __IsLoanInDate ) VAR Result = CALCULATE ( DISTINCTCOUNT ( Data[Loan] ), KEEPFILTERS ( __LoansInDate ) ) RETURN Result✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Anonymous
If you create a calculated column instead of a measure for "DOT Date" then it will be easy to summarize.Anonymous I assume Loan is a unique column in that table?
