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.
Hi,
I have a table with 2 dates. Account creation date and Account activation date. I want to count all customer ids who have account creation date in the same month as account activation date. I have to do it using a customer measure only as there are other conditions i'm adding. Is there a solution using any functions that I can use?
Solved! Go to Solution.
Seems like:
Measure =
COUNTROWS(
FILTER(
'Table',
MONTH('Table'[Creation]) = MONTH('Table'[Activation])
)
)
If not, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Seems like:
Measure =
COUNTROWS(
FILTER(
'Table',
MONTH('Table'[Creation]) = MONTH('Table'[Activation])
)
)
If not, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler This worked like magic. I did not use countrows function as that was giving me an error but just used everything from filter part and it worked like magic.
Still new to Power BI but what does countrows do here? I hope I'm not breaking anything. Also, to give you more context, this formula you gave was used in a more complex formula which had count and userrelation functions.
Here is my formula in complete:
Hi @Greg_Deckler ,
I'm trying to modify the formula a bit where the two months that I'm matching are from different tables and getting the following error: "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed" . Earlier, it was all under subscriptions table. My modified formula is:
Thanks @amitchandak @parry2k . I created date tables to be used for future calculations. Thanks for your time and help. Aprreciate it.
@newpi , create a date table. Join both dates with that. One active one inactive.
For the current one Active join should be the active date
Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
calculate(count(Table[customer]),filter(All(Table),Table[account creation date] <=_max && Table[account creation date] >=_min))
How to deal with two dates and use userelation refer
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@newpi As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
Set relationship with your date column with date dimension and one relationship will be active and other inactive, It is ok to have an inactive relationship, you can have only one active relationship between tables, to use inactive relationship in your calculation, use userelationship function
Add following measures and use it ins visuals
Count Creation Date = COUNTROWS ( Table )
Count Activation Date = CALCULATE ( [Count Creation Date], USERELATIONSHIP ( Table[ActivateDate], DateTable[Date] ) )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
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.
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |