I need help with this logic

[ajitsahoo8338]

Hello Everyone. I need help with this logic.

this is my sample data-

Project Name	Activity Number	Task_Name	Resource	Date	Recorded_Hours	EndDate
TGX	3118.1	WP03 Maintenance	MECHANICAL	7/18/2022 0:00	0	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	8/1/2022 0:00	0	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	8/8/2022 0:00	0	12/22/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	11/23/2022	1.5	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/1/2022 0:00	1	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/12/2022 0:00	1	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/19/2022 0:00	0	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/20/2022 0:00	0	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/21/2022 0:00	1	12/2/2024 0:00
TGX	3118.1	WP03 Maintenance	MECHANICAL	12/22/2022 0:00	1.5	12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	7/11/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	7/13/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	7/14/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	7/18/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	7/25/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/1/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/8/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/12/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/15/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/16/2022 0:00		12/2/2024 0:00
RTV	3121.1	WP06 Fire and Materials	MECHANICAL	8/17/2022 0:00		12/2/2024 0:00

My requirement is to find the last date where the Recorded Hours are greater than 0 for each combination of Project Name, Activity Number, Task_Name, and Resource. It should calculate the total Recorded Hours for each combination of Project Name, Activity Number, Task_Name, and Resource. If the total Recorded Hours are greater than 0, the last date where Recorded Hours are greater than 0 should be displayed. If the total Recorded Hours for each combination of Project Name, Activity Number, Task_Name, and Resource is zero or null, then the End Date should be displayed. I want it in column because I have use that date column in a date slicer

In the Above table for 

Project Name	Activity Number	Task_Name	Resource
TGX	3118.1	WP03 Maintenance	MECHANICAL

The Last Date where Recorded Hours is Above 0 is 12/22/2022. 12/22/2022 should each row for the above.  and For 

Project Name	Activity Number	Task_Name	Resource
RTV	3121.1	WP06 Fire and Materials	MECHANICAL

There are no Recorded Hours so it should show EndDate on each row for the above.

[danextian · Accepted solution]

Hi @ajitsahoo8338 

Please try these calc columns

Last Date = 
VAR _LastDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[Project Name] = EARLIER ( 'Table'[Project Name] )
                && 'Table'[Activity Number] = EARLIER ( 'Table'[Activity Number] )
                && 'Table'[Task_Name] = EARLIER ( 'Table'[Task_Name] )
                && 'Table'[Resource] = EARLIER ( 'Table'[Resource] )
                && 'Table'[Recorded_Hours] > 0
        ),
        [Date]
    )
RETURN
    COALESCE ( _LastDate, 'Table'[EndDate] )


Total Recorded Hours = 
SUMX (
    FILTER (
        'Table',
        'Table'[Project Name] = EARLIER ( 'Table'[Project Name] )
            && 'Table'[Activity Number] = EARLIER ( 'Table'[Activity Number] )
            && 'Table'[Task_Name] = EARLIER ( 'Table'[Task_Name] )
            && 'Table'[Resource] = EARLIER ( 'Table'[Resource] )
            && 'Table'[Recorded_Hours] > 0
    ),
    [Recorded_Hours]
)

[ajitsahoo8338]

Hello @danextian Thank you so much for your help

[grazitti_sapna]

Hi @ajitsahoo8338,
Here’s how you can try to solve your requirement in Power BI step-by-step:

1.Create a Calculated Column for Total Recorded Hours
To find the total Recorded Hours for each combination of Project Name, Activity Number, Task_Name, and Resource, create the following calculated column:

Total_Recorded_Hours = 
CALCULATE(
SUM('Table'[Recorded_Hours]),
ALLEXCEPT(
'Table',
'Table'[Project Name],
'Table'[Activity Number],
'Table'[Task_Name],
'Table'[Resource]
)
)

2. Create a Column to Find the Last Date Where Recorded Hours Are Greater Than 0

Now, create a calculated column to get the last date where Recorded_Hours > 0 for each combination. Use this DAX formula:

Last_Recorded_Hours_Date = 
CALCULATE(
MAX('Table'[Date]),
ALLEXCEPT(
'Table',
'Table'[Project Name],
'Table'[Activity Number],
'Table'[Task_Name],
'Table'[Resource]
),
'Table'[Recorded_Hours] > 0
)
)

This will return the last date where Recorded_Hours > 0 for the combination. If no such date exists, it will return a blank.

3. Create the Final Column for Your Required Date
To display the required date (either the Last_Recorded_Hours_Date or EndDate), create the following calculated column:

Final_Date = 
IF(
[Total_Recorded_Hours] > 0,
[Last_Recorded_Hours_Date],
MAX('Table'[EndDate])
)

This column will show:

• The Last_Recorded_Hours_Date if the total Recorded Hours for the combination is greater than 0.
• The EndDate if the total Recorded Hours is 0 or null.

I guess the Final_Date column will now have the required dates for your slicer:

For TGX > 3118.1 > WP03 Maintenance > MECHANICAL, the column will show 12/22/2022.
For RTV > 3121.1 > WP06 Fire and Materials > MECHANICAL, the column will show the EndDate.

4. Use the Final_Date Column in the Slicer
In your report:

• Add the Final_Date column to the slicer.
• This slicer will dynamically filter based on the logic you’ve applied

please ensure the Recorded_Hours column is of numeric type & replace Table in the DAX formulas with the actual name of your table in Power BI.

This setup will dynamically calculate the required date for each row and work seamlessly with your slicers

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[VahidDM]

Hi @ajitsahoo8338 

Create a calculated column that checks each combination, sums their recorded hours, and returns the appropriate date. For example:

Result Date =
VAR CurrentProject = 'Table'[Project Name]
VAR CurrentActivity = 'Table'[Activity Number]
VAR CurrentTask = 'Table'[Task_Name]
VAR CurrentResource = 'Table'[Resource]

VAR TotalHours =
CALCULATE(
    SUM('Table'[Recorded_Hours]),
    FILTER(
        ALL('Table'),
        'Table'[Project Name] = CurrentProject &&
        'Table'[Activity Number] = CurrentActivity &&
        'Table'[Task_Name] = CurrentTask &&
        'Table'[Resource] = CurrentResource
    )
)

VAR LastHourDate =
CALCULATE(
    MAXX(
        FILTER(
            ALL('Table'),
            'Table'[Project Name] = CurrentProject &&
            'Table'[Activity Number] = CurrentActivity &&
            'Table'[Task_Name] = CurrentTask &&
            'Table'[Resource] = CurrentResource &&
            'Table'[Recorded_Hours] > 0
        ),
        'Table'[Date]
    )
)

RETURN
IF(
    TotalHours > 0,
    LastHourDate,
    'Table'[EndDate]
)

This column returns the last date with recorded hours if any exist, otherwise it returns the EndDate for that combination.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[ajitsahoo8338]

Hello @VahidDM Thank you for response. Your DAX is not giving the correct output for some. For this below, it is showing the wrong date.

Project Name	Activity Number	Resource	Task_Name	Date	Recorded_Hours	EndDate	Result Date
RGS27 MRS	3053.1	MECHANICAL	GC Options Group	10/10/2024 0:00	0	10/11/2024 0:00	12/3/2024 0:00
RGS27 MRS	3053.1	MECHANICAL	GC Options Group	10/11/2024 0:00	0	10/11/2024 0:00	12/3/2024 0:00