CODEWITHSUNDEEP
countpowerbidaxdashboarddate-range
triby25
triby25

Reputation: 19

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

Upvotes: 1

Views: 3135

Answers (3)

RonaldR
RonaldR

Reputation: 1

I'm solving my problem to calculate active users on the last 15 days like this.

Active15_days = CALCULATE(
DISTINCTCOUNT(Users[ID]),
DATESBETWEEN(
    Date[Date],
    LASTDATE(Date[Date])-15,
    LASTDATE(Date[Date])
))

So in each date on the Date table the Measure will calculate the active users on the last 15 days. You can modify to show on the last 3 months too.

Upvotes: 0

triby25
triby25

Reputation: 19

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

Upvotes: 0

PowerStar
PowerStar

Reputation: 895

Create a measure in which DatesInPeriod() is used like below:

3 Month Distict Count = 
        CALCULATE(
            DISTINCTCOUNT(YourTableName[CountColumn]),
                DATESINPERIOD('Calendar'[Date]),
                    LASTDATE('Calendar'[Date]),
                        -3,
                            MONTH))

Upvotes: 1

Related Questions