Forum Discussion

kostask's avatar
kostask
Helper II
4 years ago
Solved

Filling values for missing dates for many attribute combinations

Hi guys. I had the community's support for a similar problem, but the specific one seems more complicated for me to resolve. So, we have 3 attributes (Bank, CustomerNo, AccountNo), which give many ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi kostask 
    Here is the updated solution with correct subtotals https://we.tl/t-ULVbpV6vB9

     

    FinalBalance1 = 
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR IterationTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Totals', 'Totals'[Bank], 'Totals'[Company], 'Totals'[AccountNo] ),
        ALL ( 'Calendar' )
    )
RETURN
    SUMX (
        IterationTable,
        CALCULATE (
            VAR CurrentValue =
                SUM ( 'Totals'[FinalBalance] )
            VAR CurrentAcountTable =
                CALCULATETABLE ( 'Totals', ALL ('Calendar' ) )
            VAR PreviousDatesTable =
                FILTER ( CurrentAcountTable, 'Totals'[Date] < CurrentDate )
            VAR PreviousDate =
                MAXX ( PreviousDatesTable, 'Totals'[Date] )
            VAR PreviousDateTable =
                FILTER ( PreviousDatesTable, 'Totals'[Date] = PreviousDate )
            VAR PreviousValue =
                SUMX ( PreviousDateTable, 'Totals'[FinalBalance] )
            RETURN
                - COALESCE ( CurrentValue, PreviousValue )
        )
    )

     

kostask's avatar
kostask
Helper II
4 years ago

Hi, tamerj1 .

Really very similar and based on the same problem. The solution worked fine.

The final request came with more than one attributes. I tried to solve it by making changes at ALLEXCEPT  function (added bank and customer),but didn't manage to reach the right result.

Do I miss something?

 

Thanks 

Kostas

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    kostask 
    we may connect tomorrow via zoom or teams so you can explaing your issue. Meanwhile please check the updated solution above and see if it may help.

    • kostask's avatar
      kostask
      Helper II
      4 years ago

      Hi tamerj1 

      Unfortunately, the solution does not give the right results.

      Maybe if it was possible to be adjusted to table A, we could see what is wrong.

      I tried it but without result

      Thank you 

      Kostas

       

      Thank you

       

      Kostas

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi kostask 
        Sorry I'm not able catchup what exactly is your concern. As suggested above can we connect via zoom or teams so that you can explain to me what is the issue?