topic Re: How to calculate ROY(Rest of Year) Hours in dax in DAX Commands and Tips

How to calculate ROY(Rest of Year) Hours in dax

binayjethwa — Thu, 04 Jul 2024 14:29:57 GMT

Hi ,

I have the following requirement where i need to calculate ROY(Rest of Year Hours)

I have a week start date column and my calendar starts on First monday of July and ends in June.

Based on this condition i need to calculate rest of year hours for the hours column.

I am using below measure but its returning blank. basically what ever dates are finished i need not calculate the sum of those hours only the rest of Year.

CALCULATE(

SUM('Retain Dump'[Retain Hours]),

FILTER(

ALL('Retain Dump'),

'Retain Dump'[Date] > TODAY() &&

'Retain Dump'[Date] <= DATE(YEAR(TODAY()), 6, 30)

)

Is there any other method we can achieve this. Please help.

Re: How to calculate ROY(Rest of Year) Hours in dax

rajendraongole1 — Thu, 04 Jul 2024 14:35:07 GMT

Hi binayjethwa -Can you try the below measure

ROY Hours =
VAR CurrentDate = TODAY()
VAR EndOfYearDate = DATE(YEAR(CurrentDate), 6, 30)
RETURN
CALCULATE(
SUM('Retain Dump'[Retain Hours]),
FILTER(
'Retain Dump',
'Retain Dump'[Date] > CurrentDate &&
'Retain Dump'[Date] <= EndOfYearDate
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: How to calculate ROY(Rest of Year) Hours in dax

miTutorials — Thu, 04 Jul 2024 14:35:58 GMT

There seems to be a problem in your logic, please see the dates it is returning. I guess you are trying to sum Hours until the end of Next year (Jun 2025) ?

Re: How to calculate ROY(Rest of Year) Hours in dax

binayjethwa — Thu, 04 Jul 2024 14:39:08 GMT

Hi rajendraongole1 , i am seeing the output as Blank , my date field is basically a week start date.

Re: How to calculate ROY(Rest of Year) Hours in dax

rajendraongole1 — Thu, 04 Jul 2024 14:42:03 GMT

Hi binayjethwa - Oh Ok, if you are using wstart date, please use the updated one below, check it.

ROY Hours =
VAR CurrentDate = TODAY()
VAR CurrentYear = YEAR(CurrentDate)
VAR EndOfYearDate =
IF(
CurrentDate > DATE(CurrentYear, 6, 30),
DATE(CurrentYear + 1, 6, 30),
DATE(CurrentYear, 6, 30)
)
RETURN
CALCULATE(
SUM('Retain Dump'[Retain Hours]),
FILTER(
'Retain Dump',
'Retain Dump'[Week Start Date] > CurrentDate &&
'Retain Dump'[Week Start Date] <= EndOfYearDate
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!