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?

      • kostask's avatar
        kostask
        Helper II
        4 years ago

        Hi tamerj1 

        Thank you for your reply.

        Could you please send your contact details to [email protected]

        Thank you Kostas