How to get the sum of a measure column in a table

[Anonymous]

I am using the below formula to get the sum of hours from the most current work date (Current Earned-Used Hours +(-))...I see online that there are issues getting the total when using measures.  I want to create a card with the total hours versus using the totals at the end of a table which will have the wrong sum.  How can I write a formula for the card that will give me the proper sum of the Current Earned-Used Hours +(-)?

Current Earned-Used Hours +(-) =

CALCULATE(

    SUM('ProjectDetails'[Earned - Used Hours + (-)]),

    FILTER(

        ALL(ProjectDetails[Work Date]),

        'ProjectDetails'[Work Date]=MAX('ProjectDetails'[Work Date])

    )

)

[Anonymous]

Amy...I really appreciate all of the effort you put into helping try and figure this out!

[danextian]

Hello @kentyler ,

Try this:

Measure =
SUMX (
    SUMMARIZE (
        VALUES ( ProjectDetails[Work Date] ),
        ProjectDetails[Work Date],
        "Hours", [Expression]
    ),
    [Hours]
)

More details can be found here: https://community.powerbi.com/t5/Desktop/Change-subtotal-calculation-of-matrix-from-Average-to-Sum/t...

[Anonymous]

I do not understand this portion of the formula.  Can you explain?

Thx

"Hours", [Expression]
    ),
    [Hours]
)

[kentyler]

Current Earned-Used Hours +(-) =

VAR current_work_date = MAX('ProjectDetails'[Work Date]

 VAR unused_hours = CALCULATE(

    SUM('ProjectDetails'[Earned - Used Hours + (-)]),

   FILTER(

        ALL(ProjectDetails[Work Date]),

        'ProjectDetails'[Work Date]= current_work_date)

    )

RETURN unused_hours

)

This may be a case where Slogan 6 from BISlogans.com , "Use a few simple tricks to improve your DAX code: Always use Variables"  can help. You can calculate the current date and store it in a variable before you start your CALCULATE() expression. Inside the expression you can use the variable. That way whatever changes CALCULATE makes to the filter context will not affect which date is selected as the "current_date". It has the added advantage that you can temporarily have the measure RETURN curren_work_date as a way of checking that the correct dates are being used. 

[kentyler]

Current Earned-Used Hours +(-) =

VAR current_work_date = MAX('ProjectDetails'[Work Date]

 VAR unused_hours = CALCULATE(

    SUM('ProjectDetails'[Earned - Used Hours + (-)]),

   FILTER(

        ALL(ProjectDetails[Work Date]),

        'ProjectDetails'[Work Date]= current_work_date)

    )

RETURN unused_hours

)

This may be a case where Slogan 6 from BISlogans.com , "Use a few simple tricks to improve your DAX code: Always use Variables"  can help. You can calculate the current date and store it in a variable before you start your CALCULATE() expression. Inside the expression you can use the variable. That way whatever changes CALCULATE makes to the filter context will not affect which date is selected as the "current_date". It has the added advantage that you can temporarily have the measure RETURN curren_work_date as a way of checking that the correct dates are being used. 

[Anonymous]

Alas this formula returned the same result as the original (-1370 but the actual result should be -11,408)...thoughts?