Summing up of manhours based on slicer range selection corresponding to filtered jobs

[ankurbajaj07]

In power bi, I need a measure for dax code where when user selects date range from a slicer (calender table), jobs (only the instances where it lies in the selected calender date range) and corresponding manhours (only for the selected calender date range) from my table Bi_ alloceffort_data gets displayed where the REVDATE in same table falls between the user selected range.

 I tried the follwomg codes:

1. 

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
CALCULATE(
SUM(Bi_alloceffort_data[Manhours]),
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
)

2.

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
SUMX(
FILTER(
Bi_alloceffort_data,
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
),
Bi_alloceffort_data[Manhours]
)

3. 

Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
CALCULATE(
SUM(Bi_alloceffort_data[Manhours]),
KEEPFILTERS(
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
))

But all the solutions are selecting the job based on slcier filter and then summing all the manhours (even ehen they are outside the slicer range) for the all job instances.

I need manhours only for the instance where jobs and manhours are within the selected slicer range.

Pls help

[techies · Accepted solution]

Hi @ankurbajaj07 please check this 

Selected Manhours =

CALCULATE (

    SUM ( Bi_alloceffort_data[Manhours] ),

    FILTER (

        Bi_alloceffort_data,

        Bi_alloceffort_data[REVDATE] >= MIN ( Calendar[Date] )

            && Bi_alloceffort_data[REVDATE] <= MAX ( Calendar[Date] )

    )

)

[v-tsaipranay]

Hi @ankurbajaj07 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @ankurbajaj07 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @ankurbajaj07 ,

Thanks for reaching out to the Microsoft Fabric Community.

The solution shared by @techies  is technically accurate. The DAX measure below correctly filters the Bi_alloceffort_data table to include only records where REVDATE falls within the selected date range from the calendar slicer:

Selected Manhours =
CALCULATE (
    SUM ( Bi_alloceffort_data[Manhours] ),
    FILTER (
        Bi_alloceffort_data,
        Bi_alloceffort_data[REVDATE] >= MIN ( Calendar[Date] ) &&
        Bi_alloceffort_data[REVDATE] <= MAX ( Calendar[Date] )
    )
)

This approach ensures that only the relevant job instances and corresponding manhours within the selected date range are included in the calculation.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @ankurbajaj07 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[techies · Accepted solution]

Hi @ankurbajaj07 please check this 

Selected Manhours =

CALCULATE (

    SUM ( Bi_alloceffort_data[Manhours] ),

    FILTER (

        Bi_alloceffort_data,

        Bi_alloceffort_data[REVDATE] >= MIN ( Calendar[Date] )

            && Bi_alloceffort_data[REVDATE] <= MAX ( Calendar[Date] )

    )

)

[bhanu_gautam]

@ankurbajaj07 , Try using 

dax
Selected_Manhours :=
VAR SelectedMinDate = MIN('Calendar'[Date])
VAR SelectedMaxDate = MAX('Calendar'[Date])

RETURN
SUMX(
FILTER(
Bi_alloceffort_data,
Bi_alloceffort_data[REVDATE] >= SelectedMinDate &&
Bi_alloceffort_data[REVDATE] <= SelectedMaxDate
),
Bi_alloceffort_data[Manhours]
)

[ankurbajaj07]

Its the same as option 2 which i tried but is not filtering manhours based on seletcion. When i am using this measure in table along with jobs, only job are getting filtered in line with slcier and then manhours are getting summed up against that job for all instances of the job removing the slicer criteria.