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

[newpi]

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?

[Greg_Deckler · Accepted 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.

[newpi]

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.

[newpi]

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.

[Greg_Deckler]

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.

[newpi]

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

[amitchandak]

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

[parry2k]

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