Forum Discussion
Cummulative measure
Hello commulative
I need to create a cummulative measure for how long have clients been with us. Any ideas in how I can do these?
The second question I have is that our financial year does not match calendar year, so when I tried to do the Date Became Client' in bins it won't match our fianancial year. Any ideas?
Thank you very much indeed!
Hi Anonymous ,
Please follow these steps:
1.Regarding the calculation of the time spent with the client, you can create measure and write the following dax expression:
Years of cooperation = YEAR(TODAY()) - MAX('Table'[Date_Became_Client])Create the measure and calculate the cumulative value:
cummulate = SUMX(FILTER(ALL('Table'),'Table'[Date_Became_Client] >= MAX('Table'[Date_Became_Client])),'Table'[Years of cooperation])2.Regarding the mismatch between the two dates, I created the Date_Became_Client group based on your screenshot and found that its data type is text, the reason for the mismatch between the fiscal year and the calendar year may be a data type problem, at this time it will show the error as shown in the figure below.
A text string representing a number can be converted to a number using VALUE. Similar to VALUE(MAX('Table'[Date_Became_Client (bins)])), you can refer to the documentation for more information about the value function: VALUE function (DAX) - DAX | Microsoft Learn.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Please follow these steps:
1.Regarding the calculation of the time spent with the client, you can create measure and write the following dax expression:
Years of cooperation = YEAR(TODAY()) - MAX('Table'[Date_Became_Client])Create the measure and calculate the cumulative value:
cummulate = SUMX(FILTER(ALL('Table'),'Table'[Date_Became_Client] >= MAX('Table'[Date_Became_Client])),'Table'[Years of cooperation])2.Regarding the mismatch between the two dates, I created the Date_Became_Client group based on your screenshot and found that its data type is text, the reason for the mismatch between the fiscal year and the calendar year may be a data type problem, at this time it will show the error as shown in the figure below.
A text string representing a number can be converted to a number using VALUE. Similar to VALUE(MAX('Table'[Date_Became_Client (bins)])), you can refer to the documentation for more information about the value function: VALUE function (DAX) - DAX | Microsoft Learn.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Many thanks!
Let me clarify on the second question in terms on the mismatch data. The problem is not that this is text, it was more a screen shot and an example. The issue I have is that our company fiscal year does not equal calendar year. That was my question. Our fiscal year starts on September and we finish on August. That would be
So my question is how do I relate our fiscal year to date became client, if that make sense?
Thank you very much indeed!