Using a date parameter / filter in a visual

[jamiefisher]

I have a table of absences e.g

Name	Start Date	End Date
John	01/08/23	30/08/23
John	10/03/23	11/03/23
Mark	02/08/23	04/08/23

I want users to be able to select a date e.g. 03/08/23 and to return John and Mark as being absent on this date

How best would I do this?

[jamiefisher]

Thank you for this - applied it easily to my visuals and works beautifully 👍

[jamiefisher]

Hi I have hit another issue, the measure works fine and displays a "yes" where absences cover the selected dates. I now want to do some counts and calculations but cannot do these on a measure, how do i do this?

[DOLEARY85]

Hi,

You should still be able to do calculations on a measure, do you have an example of a calculation you are working on with some sample data and i'll have a look at it

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[jamiefisher]

Hi from what you sent me yesterday I need to understand how I go about being able to count or sum from the results of a measure or do I need a new measure whith slightly more complex code?

[DOLEARY85]

Ah okay, you should be able to use sumx to create a sum and just adjust the measure slightly, try the code below:

SumMeasure =

CALCULATE(

    SUMX(

        'Table (2)',

        IF (

            SELECTEDVALUE('date'[Date]) >= 'Table (2)'[Start Date] &&

            SELECTEDVALUE('date'[Date]) <= 'Table (2)'[End Date],

            1,

            0

        )

    )

)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[jamiefisher]

Perefct - thank you for your prompt help with this! 😊

[DOLEARY85 · Accepted solution]

Hi,

If you are going to use a single value for your date selection you could use something like this:

Measure =

    IF (

        SELECTEDVALUE('date'[Date]) >= CALCULATE(MAX('Table'[Start Date]))&&

        SELECTEDVALUE('date'[Date]) <= CALCULATE(MAX('Table'[End Date])),

        "Yes",

        "No"

    )

you'll need a separate date table, i've included a PBIX file to review

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍