Forum Discussion
Creating dynamic DAX formula for below scenario
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")
Thank you so much
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.
14 Replies
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 )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=sharingAgain, Thank you so much for your help and time.
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.
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/viewAgain, Thank you so much for your help and time.
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.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.
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 goodOctober = 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.
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.
