Hi
I couldn't find a proper solution to my problem in the community.
I would like a table with the last date of the month and one measure for the current calculation of the date.
The dimension 'Date' is in the mastercalendar.
The measure counts the number of active clients between a specific date (End of each month).
Excepting result:
| Date | NB of clients |
| 31.03.2021 | Count( Distinct {< [IsActif]={1}, [ValidFrom]={"<=31.01.2021"}, [ValidTo]={">=31.01.2021"}, Year={2021}, IsMonthEnd={1}, MonthNum={"<=5"}, Month= , Day= , Date= >} [ClientNumber]) |
| 28.02.2021 | Count( Distinct {< [IsActif]={1}, [ValidFrom]={"<=28.02.2021"}, [ValidTo]={">=28.02.2021"}, Year={2021}, IsMonthEnd={1}, MonthNum={"<=5"}, Month= , Day= , Date= >} [ClientNumber]) |
| 31.03.2021 | Count( Distinct {< [IsActif]={1}, [ValidFrom]={"<=31.03.2021"}, [ValidTo]={">=31.03.2021"}, Year={2021}, IsMonthEnd={1}, MonthNum={"<=5"}, Month= , Day= , Date= >} [ClientNumber]) |
| 30.04.2021 | Count( Distinct {< [IsActif]={1}, [ValidFrom]={"<=30.04.2021"}, [ValidTo]={">=30.04.2021"}, Year={2021}, IsMonthEnd={1}, MonthNum={"<=5"}, Month= , Day= , Date= >} [ClientNumber]) |
| 31.05.2021 | Count( Distinct {< [IsActif]={1}, [ValidFrom]={"<=31.05.2021"}, [ValidTo]={">=31.05.2021"}, Year={2021}, IsMonthEnd={1}, MonthNum={"<=5"}, Month= , Day= , Date= >} [ClientNumber]) |
I tried with different methods like aggreagation, valuelist, match.... and it didn't work.
Any help will appreciable.
Regards,
Theo
