Dax

[Harmen]

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

[Anonymous · Accepted solution]

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

[nick_potts]

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.

[Harmen]

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

[nick_potts]

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

[Harmen]

The dates will always be the same.

[nick_potts]

Do you already have a date table? If so, drop that date on the page as a slicer and change TODAY() to 

SELECTEDVALUE('Dim Date'[Date])

[Bifinity_75]

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

Peildatum =

CALCULATE (

    COUNTROWS ( 'Uitkeringsgegevens' ),

    FILTER (ALL( 'Uitkeringsgegevens'),

        (

            'Uitkeringsgegevens'[startdate] <= TODAY()

                && 'Uitkeringsgegevens'[Enddate] > TODAY()

        )

    )

)

Best Regards