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 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.
Solved! Go to 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.
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.
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |