Date from one value to a chosen(filter) value

[soedholm]

Hi,

I'm trying to get total number of users from the date they joined to a specific month. This month is chosen in a Filter.

So I have the value Users[Joined_at] and All month they can choose from over sevral years. Is there a way to get:

Count of users from date Joined_At to  date chosen month?

[Phil_Seamark]

Hi @soedholm , 

It sounds like you can get this by creating a calculated measure on your table.

Count of Users = CALCULATE (COUNTROWS('yourtable'))

And then just drag your month column to the canvas and turn it into a Slicer where you can select Months.

[soedholm]

Thanks!

But that will only give me the users for one particular month. I want this scenario:

1. End-user chooses month X

2. The total number of users, from month 1 to month X is presented.

[mattbrice]

Assuming you have a separate Calendar table that is related to table[Join Date] (which is also Date filtering slicer):

Count of Users =
CALCULATE (
    DISTINCTCOUNT ( table[userid] ),
    FILTER ( ALL ( Calendar ), Calendar[Date] <= MAX ( Calendar[Date] ) )
)

[soedholm]

Unfortunalety that didn't work for me. I don't have a separate table for the Dates, and also, they're from two different tables. So for me above formula woul look like:

CALCULATE (
   DISTINCTCOUNT( Users[id]);
    FILTER(ALL(Users); (ALLSELECTED(Users[Joined Month]) <= MAX(Messages[Month Filter]))))

Which of course isn't possible.

I've tried to merge the values to be in one table in diffrent ways but no luck. Any other suggestions? Is it possible to do with the values in diffrent tables?

[soedholm]

Also "Messages[Month Filter]" is the date filtering slicer

[v-ljerr-msft]

Hi @soedholm,

Could you try the formula below to see if it works?

Count of Users =
VAR currentSelectedMonth =
    MAX ( Messages[Month Filter] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Users[id] );
        FILTER ( ALL ( Users ); Users[Joined Month] <= currentSelectedMonth )
    )

Regards

[soedholm]

Hi!

Unfortunately this still gives me users from only the selected month.