Forum Discussion

troyhimes's avatar
troyhimes
Icon for Resolver I rankResolver I
4 years ago
Solved

Total Calculation based on Total/Day for given Month

Can't seem to work this one out....   I have a table that has budget amount per day for a given month with a date value in the format month/1/year per Area.  Data below is for 'Area 1'   Us...
  • tamerj1's avatar
    tamerj1
    4 years ago

    troyhimes 
    I Plead guilty 🙂
    You are absolutely right. I overlooked the data and did not pay enough attention. I did not notice that the Tons/Day values are only in the first day while in other days are blank which is actually your original problem. 
    Please refer to updated file with the solution https://www.dropbox.com/t/RYcaBXfL2RcdQd5S
    The code is little long. I know it can be shorter and more simple by using GENERATE function but I personally still did not learn how to use it properly. Also can be solved by adding a new calculated column. Pretty sure other methods are available out there. However this one seems to work

     

     

    MMP_Tons = 
    VAR Table1 = 
        SUMMARIZE (
            Actuals_and_MonthlyPlans,
            Actuals_and_MonthlyPlans[Area],
            'Calendar'[YYMM],
            "@Tons", SUM ( Actuals_and_MonthlyPlans[MMP_Tons/Day])
        )
    VAR Table2 =
        SELECTCOLUMNS ( 
            Table1,
            "@YYMM", 'Calendar'[YYMM],
            "@@Tons", [@Tons]
        )
    VAR Table3 =
        CROSSJOIN ( 'Calendar', Table2 )
    VAR Table4 =
        FILTER ( Table3, [YYMM] = [@YYMM] )
    VAR Result =
        SUMX ( Table4, [@@Tons] ) 
    RETURN
        Result

     

     

    Please let me know if this solves your problem. Have a nice day!