Creating dynamic DAX formula for below scenario

[bikelley]

Hello, 

I am trying to calculate the running sum by Month backward. All I trying to do is take whatever number from the summary for each raw lable and minus it from the "ChangeTable". Then use that result to minus whatever the value from "Change Table" for each month. You can see below my Excel sheet, I only use the value from "Change Table" once to start. 

For a instance, 
Summary 

APAC 6851

Change Table 

2021

Q4

APAC

December   -4

November  -228

October      104

Result Calculation 

APAC Demcember [6581 - (-4) ] = 6855
APAC November  [6855 - (-228) ] = 7083
APAC Octomber  [7083 - (104) ] = 6979

Sample Power BI File 

https://drive.google.com/file/d/1v3aD_AbKB1v1N3sFjDZnudjI8EnvKY7j/view?usp=sharing 

Excel File with formular (See "Sample Data worksheet") 

https://docs.google.com/spreadsheets/d/1cR9MDeC52qlD_F1b7hLTNutVnPNsEOe1/edit?usp=sharing&ouid=11172... 

Thank you so much 

[MFelix · Accepted solution]

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:

[bikelley]

@Icey 

I am really sorry to keep bothering you, I notice that on our final result table it only calculator the first value which is December correct. Then everything is not correct. Do you think we are deducting the wrong number? 

For example, if we select APAC raw. Below should be the result. Any idea why it is giving us the wrong number? 

Backlog Hours Table 

APAC

4021

Backlog Change(Hours) Table 

APAC raw

December  = 503

November  = -226

October     = -4

September = 137

August       = -155

Expected Result 

December  = 3518 ( 4021 - 503)    This is correct

November  = 3744 (3518 - (-226)) Our table show 4247 which is not good

October     = 3748 (3744 - (-4))     Our table Value 4025, not good

September = 3611 (3748 - 137)     Our table value 3884

August       = 3766 (3611 - (-155))  Our table value 4176

Thank you so much for your help. I truly appreciate it. 

[bikelley]

@Icey  & @MFelix 

As you can see, I created the same calculation but it's giving me the wrong number. I could not find anything wrong since I did the exact same way told me to. How do you filter this to the last 13 months? I add a date filter on the filter panel did not work tho. 

I really appreciate your help on this. 

[Icey]

Hi @bikelley ,

Please check if this could meet your requirements:

Total Value = 
VAR FilterMaxDate_ =
    CALCULATE ( MAX ( AppendCombined[Date] ), ALLSELECTED () )
VAR Last13Months_ =
    DATESINPERIOD ( AppendCombined[Date], FilterMaxDate_, -13, MONTH )
VAR totalhour =
    CALCULATE (
        SUM ( AppendCombined[Hours] ),
        FILTER (
            ALLSELECTED ( AppendCombined[Date] ),
            AppendCombined[Date] IN Last13Months_
        )
    )
RETURN
    IF (
        MIN ( AppendCombined[Date] ) IN Last13Months_,
        SUM ( df_ProjectSummary[Remaining Billable Hours] ) - totalhour
    )

Best Regards,

Icey

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

[bikelley]

@Icey 

I am really sorry to keep bothering you, I notice that our final result table numbers are wrong except for December month. Do you think we are deducting the wrong numbers? 

For example, lets take APAC raw data,

December  = 3518 (4021 - 503) This correct in the final table
November  = 3744 (3518 - (-226)) Our final table shows 4274 which is not good

October      = 3748 (3744 - (-4)) Our final table shows 4025

September = 3611 (3748 - 137) Our final table shows 3884

August        = 3766 (3611 - (-155)) Our table shows 4176

Thank you so much. I truly appreciate it. 

[Icey]

Hi @bikelley ,

Please check this:

Total Value = 
VAR FilterMaxDate_ =
    CALCULATE ( MAX ( AppendCombined[Date] ), ALLSELECTED () )
VAR Last13Months_ =
    DATESINPERIOD ( AppendCombined[Date], FilterMaxDate_, -13, MONTH )
VAR totalhour =
    CALCULATE (
        SUM ( AppendCombined[Hours] ),
        FILTER (
            ALLSELECTED ( AppendCombined[Date].[Date] ),
            AppendCombined[Date].[Date] >= MIN ( AppendCombined[Date].[Date] )
        )
    )
RETURN
    IF (
        MIN ( AppendCombined[Date] ) IN Last13Months_,
        SUM ( df_ProjectSummary[Remaining Billable Hours] ) - totalhour
    )

Best Regards,

Icey

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

[bikelley]

@Icey 

For the one very last time. Can you please check my file? I am not getting the same thing as you. This is the last time I will bug you. If you have few minutes of your free time,  please take a look at my file and please let me know what is wrong. Sometimes "NA" is not giving the correct number.  Now it not doing at all. If you get a time can you please double chekc the 5 raws. Again, This the last time I will bug you and no more. 

https://drive.google.com/file/d/19WxnFZuyIQjGNjr9lNJ7CXvjRzgd6XsI/view?usp=sharing

Thank you so much and I really appreciate your help. i am not sure what is causing this to go wrong. 

[MFelix · Accepted solution]

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:

[bikelley]

@MFelix 

Thank you so much for the help. I never thought about the region, because I had already joined the table and thought it should work fine. Again, thank you so much for catching it. Otherwise, I will go crazy.

[Icey · Accepted solution]

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.

[bikelley]

@Icey  thank you so much for your help as well. I really appreciate your help on this. 

[Icey]

Hi @bikelley ,

A little modification based on @MFelix 's measure.

Total Value =
VAR minimumdate =
    CALCULATE (
        MIN ( AppendChange[Date] ),
        ALL ( AppendChange[Date] ),
        ALL ( ProjSummary[Region] ) --------------added
    )
VAR totalhour =
    CALCULATE (
        SUM ( AppendChange[Hours] ),
        FILTER (
            ALL ( AppendChange[Date].[Date] ),
            AppendChange[Date].[Date] >= MIN ( AppendChange[Date].[Date] ) ---------modified
        )
    )
RETURN
    IF (
        MIN ( AppendChange[Date].[Date] ) <= minimumdate,
        SUM ( ProjSummary[Remaining Billable Hours] ) - totalhour
    )

Best Regards,

Icey

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

[bikelley]

Hello @Icey 
Thank you so much for helping and your time. This is exactly what I want with filled spaces. As I mentioned with @MFelix I could not get the correct number like you guys. I tried changing the date and the region did not work. Can you please take a look at my file one last time? I am really stuck and could not find the error, but did the same thing you all did. 

https://drive.google.com/file/d/19WxnFZuyIQjGNjr9lNJ7CXvjRzgd6XsI/view

Again, Thank you so much for your help and time. 

[MFelix]

Hi  @bikelley 

Add the following measure:

Total Value =
VAR minimumdate =
    CALCULATE ( MIN ( AppendChange[Date] ), ALL ( AppendChange[Date] ) )
VAR totalhour =
    CALCULATE (
        SUM ( AppendChange[Hours] ),
        FILTER (
            ALL ( AppendChange[Date].[Date] ),
            AppendChange[Date].[Date] >= MIN ( AppendChange[Date] )
        )
    )
RETURN
    IF (
        MIN ( AppendChange[Date].[Date] ) <= minimumdate,
        SUM ( ProjSummary[Remaining Billable Hours] ) - totalhour
    )

[bikelley]

Hello @MFelix 

Thank you so much for helping and your time.

Really like your idea but I am running into 2 issues tho. My numbers are wrong and When I filter it last 13 months it will not work. 

The main issue is my number are wrong somehow. I did the exact same way you did, I could not find the issue. it should be something simple but no luck finding it.  Could you please take a look at my below file? 

https://drive.google.com/file/d/1T6yFIWIzRJASF5uoJ2kIl0zp46-udmST/view?usp=sharing

Again, Thank you so much for your help and time.