cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newpi
Helper V
Helper V

Create a formula in the measure to match month of the date

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?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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:

 

Same Month Activation = CALCULATE(DISTINCTCOUNT(subscriptions_v1[customer_id]),USERELATIONSHIP('Date fields'[Date],subscriptions_v1[activated_at]),subscriptions_v1[plan_id] <> "basic_gbp_free", subscriptions_v1[plan_id] <> "basic_usd_free", FILTER(subscriptions_v1,MONTH(subscriptions_v1[activated_at]) = MONTH(subscriptions_v1[started_at])))


I probably made it complex and it could be simpler but so far it looks like it worked.

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:

 

Conversions - Sign up month = CALCULATE(DISTINCTCOUNT(subscriptions_v1[customer_id]), USERELATIONSHIP('Date fields'[Date],subscriptions_v1[activated_at]), subscriptions_v1[plan_id] <> "basic_gbp_free", subscriptions_v1[plan_id] <> "basic_usd_free", FILTER(customers_v1,MONTH((customers_v1[created_at]))) = FILTER(subscriptions_v1,MONTH(subscriptions_v1[activated_at]))).

Now I'm filtering by euqating month in 2 different tables subscriptions and Customers. Can't seem to understand why the error.

The COUNTROWS was just to return the count of the rows in the FILTER 'd table. Looks like you just wanted the FILTER though clause though.

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @amitchandak @parry2k . I created date tables to be used for future calculations. Thanks for your time and help. Aprreciate it.

amitchandak
Super User IV
Super User IV

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

parry2k
Super User III
Super User III

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

 

 






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.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors