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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.