Forum Discussion

Harmen's avatar
Harmen
Regular Visitor
3 years ago
Solved

Dax

Hello, I have a dax question, how to countrows where filter is a reference date between to dates. With the next dax expression i count rows where the reference date (today) is between the startingdate and enddate. How can i make this dynamic? Not today but i want to use random dates? 

 

Peildatum =
CALCULATE (
    COUNTROWS ( 'Uitkeringsgegevens' ),
    FILTER (
        'Uitkeringsgegevens',
        (
            'Uitkeringsgegevens'[startdate] <= TODAY()
                && 'Uitkeringsgegevens'[Enddate] > TODAY()
        )
    )
)
 
Do i need a dimdate table and so yes, how to use it?
Can someone help me?
 
Kind regards,
Harmen
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Harmen ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create adate dimension table as below and apply the date field of this date dimension table on the slicer

    Date = CALENDARAUTO()

    2. Update a measure as below to get it:

    Peildatum =
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Uitkeringsgegevens' ),
        FILTER (
            'Uitkeringsgegevens',
            ( 'Uitkeringsgegevens'[startdate] <= _seldate
                && 'Uitkeringsgegevens'[Enddate] > _seldate )
        )
    )

    If the above one can't help you get the desired result, please provide some raw data in your table 'Uitkeringsgegevens' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Harmen ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create adate dimension table as below and apply the date field of this date dimension table on the slicer

    Date = CALENDARAUTO()

    2. Update a measure as below to get it:

    Peildatum =
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Uitkeringsgegevens' ),
        FILTER (
            'Uitkeringsgegevens',
            ( 'Uitkeringsgegevens'[startdate] <= _seldate
                && 'Uitkeringsgegevens'[Enddate] > _seldate )
        )
    )

    If the above one can't help you get the desired result, please provide some raw data in your table 'Uitkeringsgegevens' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi Harmen , try add "ALL" to your dax expression:

     

    Peildatum =
    CALCULATE (
        COUNTROWS ( 'Uitkeringsgegevens' ),
        FILTER (ALL( 'Uitkeringsgegevens'),
            (
                'Uitkeringsgegevens'[startdate] <= TODAY()
                    && 'Uitkeringsgegevens'[Enddate] > TODAY()
            )
        )
    )
     
    Best Regards
  • nick_potts's avatar
    nick_potts
    New Member
    3 years ago

    You want to replace 

    TODAY()

    With a random date? Or will a user be selecting the start and end date? I think knowing the end goal would help.

    • nick_potts's avatar
      nick_potts
      New Member
      3 years ago

      If you do want to replace TODAY() with another date, are those dates different or will they always be the same?

      • Harmen's avatar
        Harmen
        Regular Visitor
        3 years ago

        The dates will always be the same.

    • Harmen's avatar
      Harmen
      Regular Visitor
      3 years ago

      Thé user select a date and where the selected date is between the startdate and enddate i want to count the row.