Using comparison operators with 'Date' data type

[aJ2]

Hi.

I have created two measures for a common purpose ie i want to calculate the conversions before July 2018 from one table A and from July 2018 from another table B. When I want to split it between different months, it creates an aggregate rather than just for the particular month.

The formula for table A:- conversions_old = CALCULATE(SUM(tableA[conversions]), "MONTH('date'[date_key])<7, YEAR('date'[date_key])<=2018)

The formula for table B:- conversions_new = CALCULATE(DISTINCTCOUNT(tableB[conversions]), all_txns[platform]="app", all_txns[order_id]<282349, MONTH('date'[date_key])>6, YEAR('date'[date_key])>=2018)

The combined measure is conversions_all = conversions_new + conversions_old.

So when I split the same by year_month, it gives an aggregated value rather than splitting it by month.

Kindly help me resolve this issue.

[rajulshah]

Hello @aJ2,

Can you please share your current data vs your expected data?

[aJ2 · Accepted solution]

Hi @rajulshah

I was able to filter according to the dates by using the FILTER() function.

The formulas are- 

conversions app new = CALCULATE(SUM(tableA[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") > "2018-06"))

conversions app new = CALCULATE(SUM(tableB[conversions]), FILTER('date', FORMAT('date'[date_key], "yyyy-mm") < "2018-07"))

Thank you for your assisstance.

[v-piga-msft]

Hi @aJ2,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry