Search code examples
countpowerbidaxdashboarddate-range

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?

I attach an image with an example of what I want to do.

enter image description here

Solution

  • My problem has been resolved with the following dax code :

    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)))

    thanks for the help mr. balaji