Forum Discussion
Power BI cumulative count between date range
I am trying to make a board that tells me the active users in the last 3 months for each month, I have a calendar table with the last day of each month of 2020 and the idea is that when I click on 03/31/2020 I will present the data from a distinctive count of users who have logged in in the last 3 months.
Each bar must present:
- January (count from 11/01/2019 to 01/31/2020)
- February (count from 12/01/2019 to 02/29/2019)
- March (count from 01/01/2020 to 03/31/2020)
If I click on 04/30/2020
- January (count from 11/01/2019 to 01/31/2020)
- February (count from 11/01/2019 to 01/31/2020)
- March (count from 12/01/2019 to 02/29/2019)
- April (count from 02/01/2020 to 04/30/2020)
How could I do this with a measure in DAX?
Here is an Example of what I want to do.
Hi perlac17 ,
Please change the measure like below to see whether it work or not.
Login Count = CALCULATE(DISTINCTCOUNT(user_logins[key]), FILTER(ALLEXCEPT(user_logins,user_logins[chanel]), user_logins[login_date]>=date(YEAR(MIN(calendar[Date])), month(MIN(calendar[Date]))-2,1) && user_logins[login_date]<date(YEAR(MIN(calendar[Date])), month(MIN(calendar[Date]))+1,1)))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Thanks for your quick response,
Your example has helped me, but I still have problems showing the data, I still can't get it to show as I need it.
I attach a link to a zip file containing my board and a text file with an example of how the bars should show.
daxCommunity Support
Hi perlac17 ,
Please change the measure like below to see whether it work or not.
Login Count = CALCULATE(DISTINCTCOUNT(user_logins[key]), FILTER(ALLEXCEPT(user_logins,user_logins[chanel]), user_logins[login_date]>=date(YEAR(MIN(calendar[Date])), month(MIN(calendar[Date]))-2,1) && user_logins[login_date]<date(YEAR(MIN(calendar[Date])), month(MIN(calendar[Date]))+1,1)))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
