cancel
Showing results for
Did you mean:
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
Super User IV

Seems like:

Measure =
COUNTROWS(
FILTER(
'Table',
MONTH('Table'[Creation]) = MONTH('Table'[Activation])
)
)

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.

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

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

Proud to be a Super User!

7 REPLIES 7
Super User IV

Seems like:

Measure =
COUNTROWS(
FILTER(
'Table',
MONTH('Table'[Creation]) = MONTH('Table'[Activation])
)
)

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.

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

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

Proud to be a Super User!

Helper V

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.
Helper V

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.
Super User IV
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.

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

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

Proud to be a Super User!

Helper V

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

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://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...

Proud to be a Super User!

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/

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!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Microsoft named a Leader in The Forrester Wave

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