Forum Discussion

ArtRijnvicus2's avatar
ArtRijnvicus2
Frequent Visitor
2 years ago

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

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.

 

13 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      ArtRijnvicus2
      Frequent Visitor
      2 years ago

      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:

        

       

       

        

  • ArtRijnvicus2's avatar
    ArtRijnvicus2
    Frequent Visitor
    2 years ago

    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

     

     

     

     

      • ArtRijnvicus2's avatar
        ArtRijnvicus2
        Frequent Visitor
        2 years ago

        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

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.