Forum Discussion

chlxco's avatar
chlxco
Frequent Visitor
4 years ago
Solved

Measure using time not totalizing - Complex one.. I guess.

good morning community. Im facing the following challenge. Consider this model Table Item Time Start Date End Date Time x month prj A  30  jan-2021  mar-2021  10 prj B  120  feb-...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, chlxco 

     

    According to your description, Is the 'total' displayed incorrectly?

    If yes, you can try:

    Total Hours =
SUMX (
    SUMMARIZE (
        Table,
        [Start Date],
        [End Date],
        [Time x Month],
        "a",
            CALCULATE (
                SUM ( Table[Time x Month] ),
                FILTER (
                    VALUES ( Table[Start Date] ),
                    Table[Start Date] <= MAX ( Dates[Date] )
                ),
                FILTER ( VALUES ( Table[End Date] ), Table[End Date] >= MIN ( Dates[Date] ) )
            )
    ),
    [a]
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

chlxco's avatar
chlxco
Frequent Visitor
4 years ago

Hi Amit

 

thanks for sharing this. I went through the file and blog but the issue is not related to the ability to split days/months or even sum hours across a timeframe. that purpuse is achieved by the calculated measure Total Hours. The issue Im facing is about summing the hours across a date range. If I select 1 single month, it does the job, but when I select +1 months, the calculation just not retain the total of months. Maybe related to what I asked to YukiK below, how can I use a SUM with the calculated field Total Hours, as the DAX only allows Columns as parameter? thx again