Time Intelligence not working

[samoberoi]

Hi all,

I am using Userelationship function in my DAX  below & it seems to be working fine to sum up the values on column A from Table_Eng on the condition of if the column B values from the table Table_comb match with Column E from Table_Eng. The issue i am facing is in the calculated column DAX below where i am trying to limit total to only include values in dates greater than or equal to current month day 1 and in dates less than current month+1 (which will be the next month) after i created an inactive 1-2-1 relationship between the two tables of Table_Comm & Table_Eng. In order to now calculate it within a specific time period and change to days e.g. it should only calculate it for the month of June that means from 01/06/2024 till 30/06/2024 and convert the hours to days. I used the following DAX  after linking the date table with Table_Ass as showing in the picture of data model below, but it doesn't seem to work and it doesn't calculate the sum between that date range etc.

DAX = CALCULATE (

             SUM ( Table_Eng[Column A]),

USERELATIONSHIP ( Table_Comm[Id], Table_Eng[Id]),

DATESBETWEEN(Dates[Date],TODAY(),EOMONTH(EDATE(TODAY(),1),0)/7.5))

I would like to know if i am doing anything wrong in the data model or in the DAX. Will appreciate the help. Its excel version of the formula is as, and it should work something like this. 
=SUM(SUMIFS(Table_Eng!$K:$K,Table_Eng!$G:$G,Table_Comm!$A29,Table_Eng$C:$C,">="Date!$C$1,Table_Eng!$C:$C,"<"Date!$D$1)/7.5)

Thanks for your help in advance.

Regards

[rajendraongole1 · Accepted solution]

Hi @samoberoi - Can you try the below measure:

TotalHoursWithinDateRange =
CALCULATE(
SUM('Table_Eng'[Column A]),
USERELATIONSHIP('Table_Comm'[Id], 'Table_Eng'[Id]),
DATESBETWEEN(
'Dates'[Date],
DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -- Start of current month
EOMONTH(TODAY(), 0) -- End of current month
)
) / 7.5 -- Convert hours to days

Here , i have highlighted the changes. Pleas use the above and let know.

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

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks fot the solution @rajendraongole1  privided and i want to offer some more information for user to refer to.

hello @samoberoi , you can try the following measure

Measure =
VAR a =
    CALCULATE (
        SUM ( Table_Eng[Column A] ),
        USERELATIONSHIP ( Table_Comm[Id], Table_Eng[Id] ),
        DATESBETWEEN (
            Dates[Date],
            EOMONTH ( TODAY (), -1 ) + 1,
            EOMONTH ( TODAY (), 0 )
        )
    )
RETURN
    DIVIDE ( a, 7.5 )

Best Regards!

Yolo Zhu

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

[rajendraongole1 · Accepted solution]

Hi @samoberoi - Can you try the below measure:

TotalHoursWithinDateRange =
CALCULATE(
SUM('Table_Eng'[Column A]),
USERELATIONSHIP('Table_Comm'[Id], 'Table_Eng'[Id]),
DATESBETWEEN(
'Dates'[Date],
DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -- Start of current month
EOMONTH(TODAY(), 0) -- End of current month
)
) / 7.5 -- Convert hours to days

Here , i have highlighted the changes. Pleas use the above and let know.

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

[samoberoi]

Hi,

Is it possible to make it a bit more dynamic? For example it should calculate the sum for each month of the year January, Feb, March, etc individually by itself rather than by creating different measures for each separate month?

Thanks a lot for your help. Your solution as well as Yolo Zhu's solution for the last query worked perfectly fine for the last query.
Regards