Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Enam
Regular Visitor

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=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 :

Enam_0-1722607799485.png

 

Notes :

- Date format is yyyy/mm/dd.

- Monday in the pbix represents next Monday for Star_Date.

2 ACCEPTED SOLUTIONS
Gabry
Super User
Super User

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 😉

View solution in original post

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

View solution in original post

10 REPLIES 10
Enam
Regular Visitor

Hi @Gabry ,

Now the dispacth is ok but the total disappear

Enam_0-1722871173406.png

 

 

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
Regular Visitor

Thank you so much for your time.

Enam
Regular Visitor

Thank you

Enam
Regular Visitor

Thank you

Enam
Regular Visitor

Hi, thank you it shoul be a measure.

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

Gabry
Super User
Super User

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
Regular Visitor

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(ClasseurClasseur[Activity]),
    Classeur[Start_Date] <= _mon,
    Classeur[End_Date] >= _mon
)
Enam_1-1722624610864.png

 

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.