Ok. Here is a pbix file that has your example data. I created two measures and a calculated column and ended up with the values you are looking for.
Proud to be a Super User! |  |
Hi @Krishna_Newuser ,
I think this happens because you have data from multiple years.
The following DAX expression does not take the year into consideration, the maximum month is 6, and the data for 2023 and 2024 are calculated.
The Table data is shown below:
Use the following DAX expression to create a measure
Measure =
VAR _summarizeTable = ADDCOLUMNS(SUMMARIZE('Table','Table'[Txn Dt],"average",AVERAGE('Table'[New Adoption])),"month",MONTH([Txn Dt]),"year",YEAR([Txn Dt]))
VAR _maxMonthNumber = MONTH(MAX('Table'[Txn Dt]))
VAR _maxYearNumber = YEAR(MAX('Table'[Txn Dt]))
VAR _rowscount = COUNTROWS(FILTER(_summarizeTable,[month] = _maxMonthNumber && [year] = _maxYearNumber))
VAR _averageOfNewAdoption = SUMX(FILTER(_summarizeTable,[month] = _maxMonthNumber && [year] = _maxYearNumber),[average])
RETURN DIVIDE(_averageOfNewAdoption,_rowscount,0)Final output
If the error persists, can you share the .pbix file without the sensitive data?
Best Regards,
Wenbin Zhou
If this post helps,Please mark me and @jgeddes as the solution.
If you have your date column formatted as above like '5th May 2024' you will need to change it to an acutal date format for it to work.
Proud to be a Super User! | |
Are you importing the data or is it coming in via Direct Query?
Proud to be a Super User! | |
Hi,
Data is available in the sharepoint and in CSV format, we are just importing the data.
I used the below query which you have asked to modify it, below is the screenshot of the error. It didnt worked, unable to figure it out to make any changes here.
i used this query and its showing the value as 49% but based on june 2024 it has to be 47.
I may be misunderstanding a bit. Where are you getting June data from? In your original post you provided data that falls entirely in May.
Proud to be a Super User! | |
Ok. Thank you for the clarification. That is what I understund initially. Can you share a sample of the new June data in the same format as your original post? Otherwise I will not be able to troubleshoot why the June results do not align.
Proud to be a Super User! | |
Ok. Here is a pbix file that has your example data. I created two measures and a calculated column and ended up with the values you are looking for.
Proud to be a Super User! | |
Hi,
Thank you for the help!!! When i added the whole data in the PBIX which you shared, its providing the result of 49%. I have used the same measure which you have given, if we added the overall data, i think some challenge occurs.
Date Converstion not required since my date format is already updated in Long date format.
If you replace all instances of the DateConversion column in my measure with the TxnDt column in your set it "should" work.
Proud to be a Super User! | |
Though i have the same data format, do i need to create the data conversion?
If so, i have done that but it shows error.
Hello,
Thank you for the reply. Request you to check on the previous response, i have used the 1 year data which i have in the system by trying the measue which updated in the report. It shows the overall % of 49 instead of 47. Can you help on this please.
Hi @Krishna_Newuser ,
I think this happens because you have data from multiple years.
The following DAX expression does not take the year into consideration, the maximum month is 6, and the data for 2023 and 2024 are calculated.
The Table data is shown below:
Use the following DAX expression to create a measure
Measure =
VAR _summarizeTable = ADDCOLUMNS(SUMMARIZE('Table','Table'[Txn Dt],"average",AVERAGE('Table'[New Adoption])),"month",MONTH([Txn Dt]),"year",YEAR([Txn Dt]))
VAR _maxMonthNumber = MONTH(MAX('Table'[Txn Dt]))
VAR _maxYearNumber = YEAR(MAX('Table'[Txn Dt]))
VAR _rowscount = COUNTROWS(FILTER(_summarizeTable,[month] = _maxMonthNumber && [year] = _maxYearNumber))
VAR _averageOfNewAdoption = SUMX(FILTER(_summarizeTable,[month] = _maxMonthNumber && [year] = _maxYearNumber),[average])
RETURN DIVIDE(_averageOfNewAdoption,_rowscount,0)Final output
If the error persists, can you share the .pbix file without the sensitive data?
Best Regards,
Wenbin Zhou
If this post helps,Please mark me and @jgeddes as the solution.
You can try the following and see if it gives you your desired result.
Instead of MONTH(NOW()) and YEAR(NOW()) you can use MONTH(MAXX(ALL('Adoption Trend_Dup'), [TxnDt])) and YEAR(MAXX(ALL('Adoption Trend_Dup'), [TxnDt])).
This will take the month and year from the max date that exists in your table.
Proud to be a Super User! | |
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
We want your feedback!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
