Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

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:

 

LoanNumberDOT Date
0017/9/2021
0027/2/2021
0037/2/2021
0046/5/2021
0056/5/2021
0067/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 DateNumber of Loans
6/5/20212
7/2/20213
7/9/20211

 

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?

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@Anonymous I assume Loan is a unique column in that table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

yes, it's unique 🙂

Fowmy
Super User
Super User


@Anonymous 

If you create a calculated column instead of a measure for "DOT Date" then it will be easy to summarize.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

I can't create a calculated column because I'm working within a cube and I don't have admin rights 😞

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors