Forum Discussion

jamiefisher's avatar
jamiefisher
Helper I
2 years ago
Solved

Using a date parameter / filter in a visual

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....
Need Help
  • DOLEARY85's avatar
    DOLEARY85
    2 years ago

    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 👍
jamiefisher's avatar
jamiefisher
Helper I
2 years ago

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

jamiefisher's avatar
jamiefisher
Helper I
2 years ago

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's avatar
    DOLEARY85
    Resident Rockstar
    2 years ago

    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's avatar
      jamiefisher
      Helper I
      2 years ago

      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's avatar
        DOLEARY85
        Resident Rockstar
        2 years ago

        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 👍