Creating dynamic DAX formula for below scenario

Hi bikelley ,

I have not answered this post because you have been working with the community support, but the problem in your calculation is the fact you are using two different region columns for the context of your measure so the calculation gets incorrect:

The top table is made with the region adj from the Appended combined table making the total hours different from the bottom table that is done using the projectsummary. Since you matrix is done with the summary table this will give you the incorrect results.

Instead of having (APAC DEcember) 4033 - 407 = 3.626 you get 4033 - 571 = 3.462

For this to give you the correct result redo your calculations to:

Total Value New_ = 
VAR minimumdate =
    CALCULATE (
        MIN ( 'AppendCombined (2)'[Date] ),
        ALL ( 'AppendCombined (2)'[Date] )
    )
VAR totalhour =
    CALCULATE (
        CALCULATE (
            SUM ( 'AppendCombined (2)'[Hours] ),
            CROSSFILTER ( 'AppendCombined (2)'[Project Name], 'df_ProjectSummary (3)'[Project Name], NONE ),
            'AppendCombined (2)'[Region_Adj]
                = SELECTEDVALUE ( 'df_ProjectSummary (3)'[Region Adj] )
        ),
        FILTER (
            ALL ( 'AppendCombined (2)'[Date].[Date] ),
            'AppendCombined (2)'[Date].[Date] >= MIN ( 'AppendCombined (2)'[Date] )
        )
    )
RETURN
    IF (
        MIN ( 'AppendCombined (2)'[Date].[Date] ) <= minimumdate,
        SUM ( 'df_ProjectSummary (3)'[Remaining Billable Hours] ) - totalhour
    )

Has you can see below there is a difference between both calculations believe that the last one (Total Value _) is the one you want.

Context is very important and in this case you changed the context of the calculation and you get the incorrect result, based on what I see from your data you should have dimensions for the regions and for the dates that way your calculations would be correct.

If you check the two tables below you can see the difference in values:

Hi bikelley ,

Please check MFelix 's reply. He finds your issue. This is indeed the problem.

---

 

but the problem in your calculation is the fact you are using two different region columns for the context of your measure so the calculation gets incorrect:

 

---

After changing this, both MFelix's and my measures could give you the result you want.

Best Regards,

Icey

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