Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago

Dynamic calculation

Ive got a visual based on a few measurements and when I use the slider the outcome for those measurements should change. But here is my problem.   Ive got a datasheet with dates in them, adresses ...
Need Help
RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago

amitchandak you think you can help me figure this problem out ?

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      RonaldvdH , Try this

       

      Aantal HP plastic Gereed = if(isfiltered('Civiel/HAS planning'[Completion date]) Countrows(
      Filter(
      'Civiel/HAS planning',
      'Civiel/HAS planning'[gesloten]<>BLANK()
      &&'Civiel/HAS planning'[activiteit]="Civiel"
      && not(isblank('Civiel/HAS planning'[Completion date]))
      )
      ) , Countrows(
      Filter(
      'Civiel/HAS planning',
      'Civiel/HAS planning'[gesloten]<>BLANK()
      &&'Civiel/HAS planning'[activiteit]="Civiel"
      )
      ) ) )

       

      I need data with all these columns with expected output sample to work on file

      • RonaldvdH's avatar
        RonaldvdH
        Post Patron
        4 years ago

        The outcome still isn't what I expected and I understand this is a difficult problem and it's hard to explain what I'm looking for but i'm gonna try explaining.

         

        1. I have a table with 10.000+ records, in this case addresses.
        2. If an address is planned they have a Startdate and an enddate
        3. There are also adresses with NO plandate (or enddate)
        4. Every address (when completed) has an date in the column Closeddate
        5. So I have adresses which:
          Addresses which aren't planned at all (= startdate and enddate have no values) 
          Addresses that are planned (= startdate and enddate have values and NO Closeddate) 
          Addresses that are closed (= startdate and enddate have values and a Closeddate)
        6. The slicer is based on the date in the column Startdate

         

        The idea is that when I change the date in the slicer these measurements calculated (within the daterange) how many addresses have been completed.

        The difficulty is that the daterange in the slicer has a beginning and an end and should only calculated/count the addresses that fit the filter within that range.

         

        For example when I set the slicer to a daterange, let's say, between 01-04-22 and 25-04-22 it should only filter those adresses with an Enddate between that range. So if an address has an Enddate of 27-04-22 it should be counted, same goes for an address with an Enddate of 25-03-22

        When I set the slicer to NO range so basically the min/max value of my datetable it should give the current status