Forum Discussion

Enam's avatar
Enam
Regular Visitor
2 years ago
Solved

DAX help needed

Here is the link to a sample pbix file https://drive.google.com/file/d/1J4C3V4A98IwZpQbubTlF6tPWlwXQG2Ir/view?usp=sharing

or to Excel file https://docs.google.com/spreadsheets/d/18SXUttN51bZLLPfKXnV41RN4lQq62fku/edit?usp=drive_link&ouid=103276039085101602984&rtpof=true&sd=true

 For every Monday, I want number of « Activity » WHERE « Star_Date » is previous or equal to each specific Monday AND « End_Date» is posterior or equal to each specific Monday.

For every Monday, I want number of « Activity » WHERE « Star_Date » is previous or equal to each specific Monday AND « End_Date» is posterior or equal to each specific Monday.

Excpected result :

 

Notes :

- Date format is yyyy/mm/dd.

- Monday in the pbix represents next Monday for Star_Date.

Need Help
  • Gabry's avatar
    Gabry
    2 years ago

    Hello Enam 

     

    Do you need a measure or it's ok a calculated column?

     

    This is the formula for calculated column

     

    Count of Activitys =

    var _start = Classeur[Start_Date]
    var _end= Classeur[End_Date]
    var _mon= Classeur[Monday]
    RETURN
    CALCULATE(COUNT(Classeur[Activity]), all(Classeur),Classeur[Start_Date]<=_mon, Classeur[End_Date]>=_mon)
     
    Instead this could be your measure
     
    Count of Activities Measure =
    VAR _mon = SELECTEDVALUE(Classeur[Monday])
    RETURN
    CALCULATE(
        COUNT(Classeur[Activity]),
        ALL(Classeur),
        Classeur[Start_Date] <= _mon,
        Classeur[End_Date] >= _mon
    )

    let me know 😉
  • Gabry's avatar
    Gabry
    2 years ago

    I see. In that case I think you need to create another table, a Mondays table, use it in the column. I attach a sample pbix

     

    I think like this can work but I don't know if it's a feasible solution for you. Let me know, maybe I can think of other solutions

10 Replies

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hello Enam 

     

    Do you need a measure or it's ok a calculated column?

     

    This is the formula for calculated column

     

    Count of Activitys =

    var _start = Classeur[Start_Date]
    var _end= Classeur[End_Date]
    var _mon= Classeur[Monday]
    RETURN
    CALCULATE(COUNT(Classeur[Activity]), all(Classeur),Classeur[Start_Date]<=_mon, Classeur[End_Date]>=_mon)
     
    Instead this could be your measure
     
    Count of Activities Measure =
    VAR _mon = SELECTEDVALUE(Classeur[Monday])
    RETURN
    CALCULATE(
        COUNT(Classeur[Activity]),
        ALL(Classeur),
        Classeur[Start_Date] <= _mon,
        Classeur[End_Date] >= _mon
    )

    let me know 😉
    • Enam's avatar
      Enam
      Regular Visitor
      2 years ago

      Hello @Gabry , the total is ok but not the dispatch by Activity even with a slight change to the measure you kindly provided

      Count of Activities Measure =
      VAR _mon = SELECTEDVALUE(Classeur[Monday])
      RETURN
      CALCULATE(
          COUNT(Classeur[Activity]),
          ALLEXCEPT(Classeur, Classeur[Activity]),
          Classeur[Start_Date] <= _mon,
          Classeur[End_Date] >= _mon
      )

       

       

      • Gabry's avatar
        Gabry
        Super User
        2 years ago

        I see. In that case I think you need to create another table, a Mondays table, use it in the column. I attach a sample pbix

         

        I think like this can work but I don't know if it's a feasible solution for you. Let me know, maybe I can think of other solutions

  • Enam's avatar
    Enam
    Regular Visitor
    2 years ago

    Hi, thank you it shoul be a measure.

    • Gabry's avatar
      Gabry
      Super User
      2 years ago

      Enam  I previously edited the answer and wrote you also the measure

  • Enam's avatar
    Enam
    Regular Visitor
    2 years ago

    Hi Gabry ,

    Now the dispacth is ok but the total disappear

     

     

    • Gabry's avatar
      Gabry
      Super User
      2 years ago

      I see, adjust the measure like this:

       

       

      Count of Activities Measure =
      VAR _mon = SELECTEDVALUE('Mondays'[Monday])
      VAR _ac = SELECTEDVALUE(Classeur[Activity],"Total")
      RETURN
      IF(_ac="Total",
      CALCULATE(
          COUNTROWS(Classeur),
          ALL(Classeur),
          Classeur[Start_Date] <= _mon,
          Classeur[End_Date] >= _mon)
      ,
      CALCULATE(
          COUNTROWS(Classeur),
          ALL(Classeur),
          Classeur[Start_Date] <= _mon,
          Classeur[End_Date] >= _mon,
          Classeur[Activity] = _ac
      )
      )