Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ArtRijnvicus2
Frequent Visitor

DAX measure: Count from date

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
Pbi Model.jpgtabel.jpg

46846d75-9883-4acf-bb65-5f20d00e7bad.png

Report.pngPbi Model.jpgtabel.jpging 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.


 

12 REPLIES 12
ArtRijnvicus2
Frequent Visitor

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

 

 

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

 

 

tabel.jpg

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






 

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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:

 

desired outcome.jpg

 

 

report: 

Report.jpg

 

 

 

 

model:

Model.jpg

 

 tables:

table date.jpg

table Members.jpg

table Leaderes.jpg

  

 

 

  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors