DAX measure: Count from date

[ArtRijnvicus2]

good morning all,
I have been working in PowerBi for 3 years and have already published several (beautiful) reports in an app.
However, now I am faced with the following challenge:

I want to count the number of BSN numbers, per month and per supervisor. from the date a person is supervised.
So as an example with "Alex": Alex has only been coaching people since February 1, 2024, so January must be empty here and counting can only be done from February 2024 (42 people). in March the number should also be 42. (Suppose 1 person started guidance in March, then the number must be 43 in March.) Etc.

Who can help me with the correct dax query?! thank you in advance!

Regards,
Art

ing people since February 1, 2024, so January must be empty here and counting can only be done from February 2024 (42 people). in March the number should also be 42. (Suppose 1 person started guidance in March, then the number must be 43 in March.) Etc.

[v-tianyich-msft]

Hi @ArtRijnvicus2 ,

Have you solved your problem? I did notice that you are using a between style slicer but using a selecetedvalue in the expression, this will not return the correct date, please try using min and then control the minimum time to try.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ArtRijnvicus2]

Thank you for your quick response some_bih!

Unfortunately, the measure did not deliver the desired result. the values ​​have remained empty.

(same result as the measure AantalVanafDatum).
what do you mean by "not much information from your side about the model"? iv made printscreens of the model right?

Greetings,

Art

[some_bih]

Hi @ArtRijnvicus2 you wrote details for Alex, but I could not reach that details

[ArtRijnvicus2]

Does this image work for you?

person 1 joined this data on 01-02-2024 so the measure should count it in februari, march, etc. (but not in januari).

Greetings,

Art

 

[some_bih]

Hi @ArtRijnvicus2 

For me issue is that you use USERELATIONSHIP, which is ok for non-active relationship. Still, your language is not my native, and not English, so only picture is not usefull.

What I could notice is that you use in original measure _Dim_Date[Year]), which I "rewrite" to SELECTEDVALUE. If you can not share model, due to sensitivity infos, play a bit with this part _Dim_Date[Year]) and SELECTEDVALUE. 

[ArtRijnvicus2]

hello Some_Bih,

iv made a "Community test bpi" file with some made up numbers so i can share it with you. altough i can not share it in a reply. how can i share it with you?

greetings,

Art

[some_bih]

Hi @ArtRijnvicus2 if you have Office 365 share link for file or use some other tools (dropbox...)

[ArtRijnvicus2]

Good morning,

it took a while... 😛 but I cleaned out my dropbox and made room for the .pbi file! If all goes well, you can view and edit the "test" file via the link below. I hope I have formulated my question clearly and that you have the solution...!!

Greetings,

Art

https://www.dropbox.com/scl/fo/kfog04qmmiqblx58e6fv6/h?rlkey=pmku3lumvb4nhpuf56dn9aywm&dl=0

[some_bih]

Hi @ArtRijnvicus2 I will check it on my home network in following days, and let you know.

[some_bih]

Hi @ArtRijnvicus2 for your example file, as it is example, what is expected output for interaction / selection for some period for Alex and other Leaders?

I am trying to understand your request.

[ArtRijnvicus2]

the desired outcome is in the excel tab "wanted"

as you can see with Alex, in 2023 he has 0 members in all months. As soon as the filter is set to 2024, you will see that Alex gets members in February (month 2) and this increases throughout the year.

I hope I have been able to clarify my question.

regards,

Art

[some_bih]

Hi @ArtRijnvicus2 try Test version (not much information from your side about model + check wording to your model)

Test v2=

VAR __period_selected=SELECTEDVALUE(_Dim_Date[Year])

RETURN

CALCULATE (

COUNTA(ESF[BSN]),

USERELATIONSHIP (_Dim_Date[Date], ESF[Datum aanvang begeleiding]),

FILTER (

ESF,

ESF[Datum aanvang begeleiding]<=__period_selected

)

)

[ArtRijnvicus2]

Since i can not share excel and pbix files, ill try it with print screens.

the dax measure i used is the following:

Selected value Unique BSN =

CALCULATE(

DISTINCTCOUNT(

    'Members'[BSN]),

    filter(

        'Members',

        'Members'[From] <=SELECTEDVALUE(DaxDateTabel[Date])

   ))

I want the matrix to show all the leaders, even if the members are 0 in a period. I want DAX to count a member when the date in Members[from] is reached. So i want them to count when its >= the selected year and month in the matrix. 

I hope my question has been stated clearly. If something is not completely clear, please let me know!

Desired outcome:

report: 

 

model:

 tables: