DAX help needed

[Enam]

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=10...

 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.

[Gabry · Accepted solution]

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]

Hi @Gabry ,

Now the dispacth is ok but the total disappear

[Gabry]

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

)

)

[Enam]

Thank you so much for your time.

[Enam]

Thank you

[Enam]

Thank you

[Enam]

Hi, thank you it shoul be a measure.

[Gabry]

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

[Gabry · Accepted solution]

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]

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 · Accepted solution]

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