Forum Discussion

bikelley's avatar
bikelley
Icon for Helper IV rankHelper IV
4 years ago
Solved

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 th...
Need Help
Show and Tell
bikelley's avatar
bikelley
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
Icey
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    bikelley
    Icon for Helper IV rankHelper IV
    4 years ago

    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's avatar
      Icey
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        bikelley
        Icon for Helper IV rankHelper IV
        4 years ago

        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.