Is there a way to ADD/Insert multiple columns into a single table? OR elegant solve of rolling bal

[Anonymous]

Please see above, I need to have a rolling balance and have rates calcing against a starting balance and work down, then the starting balance of the next month needs to be the ending of the previous month.  The only way i have been able to do this is to create columns and manually forcing the calculation by referencing the columns.   Is there a more elegant way to do this?

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, We cannot understand your data model clearly, Could you please provide a mockup sample  based on fake data or describle the fields of each tables and the relations between tables simply? Which  way you want to show the expected table, show in report visual or make it a actual table? Please upload your files to One Drive and share the link here.
 
Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Hi there,
Please see below for a detailed walkthrough.  The cumulative doesnt work because i have to use the ending balance within the same month.

[Anonymous]

Hi @amitchandak your solution doesn't work as it doesn't allow me to use that **bleep** balance within the same month. I have tried something similar before.  I have upload a more detailed sample though, hope you can help, have been struggling with this for awhile.

[Anonymous]

any help here?  Or is this just not possible?

[v-lid-msft]

Hi @Anonymous ,

We can try to create a calculated table to meet your requirement:

Output =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( 'Table A'[Account] ),
        { "Rate1", "Rate2" },
        DISTINCT ( 'Table B'[Date] )
    ),
    "Output",
    VAR d = [Date]
    VAR initValue =
        CALCULATE (
            SUM ( 'Table A'[Amount] ),
            'Table A'[Account] = EARLIER ( [Account] )
        )
    RETURN
        SWITCH (
            [Value],
            "Rate1",
            VAR r1 =
                PRODUCTX ( FILTER ( 'Table B', [Date] <= d ), 1 - [Rate1] )
            VAR r2 =
                PRODUCTX ( FILTER ( 'Table C', [Date] < d ), 1 - [Rate2] )
            RETURN
                IF ( r1 = 0, 1, r1 )
                    * IF ( r2 = 0, 1, r2 ) * initValue,
            "Rate2",
            VAR r1 =
                PRODUCTX ( FILTER ( 'Table B', [Date] <= d ), 1 - [Rate1] )
            VAR r2 =
                PRODUCTX ( FILTER ( 'Table C', [Date] <= d ), 1 - [Rate2] )
            RETURN
                IF ( r1 = 0, 1, r1 )
                    * IF ( r2 = 0, 1, r2 ) * initValue
        )
)

Best regards,

[Anonymous]

Thanks for this...one more ask, if I want Rate 1 and Rate 2 to multiplied against the same number, in this example:  99,999,999.00 , what would i need to change in the code?

[v-lid-msft]

Hi @Anonymous ,

We can use the following dax to meet your requirement:

Output =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( 'Table A'[Account] ),
        { "Rate1", "Rate2" },
        DISTINCT ( 'Table B'[Date] )
    ),
    "Output",
    VAR d = [Date]
    VAR A = [Account]
    VAR initValue =
        CALCULATE (
            SUM ( 'Table A'[Amount] ),
            'Table A'[Account] = EARLIER ( [Account] )
        )
    RETURN
        SWITCH (
            [Value],
            "Rate1",
            VAR r1 =
                PRODUCTX ( FILTER ( 'Table B', [Date] <= d && [Account] = A ), 1 - [Rate1] )
            RETURN
                IF ( r1 = 0, 1, r1 ) * initValue,
            "Rate2",
            VAR r2 =
                PRODUCTX ( FILTER ( 'Table C', [Date] <= d && [Account] = A ), 1 - [Rate2] )
            RETURN
                IF ( r2 = 0, 1, r2 ) * initValue
        )
)

Best regards,

[Anonymous]

This is SO CLOSE! The only issue is that i have more than one client not just one.  I modified the example to the below.

I tried to modify your code to add an account condition like below using an EARLIER condition but it doesn't work.  Sorry for the many questions but this will be so helpful once figured out.

This is so close can you please help me solve it? Also, if I want to add more rates in would i just replicate the code?

Thanks for your help!

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following calculated table to meet your requirement:

Output = 
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( 'Table A'[Account] ),
        { "Rate1", "Rate2" },
        DISTINCT ( 'Table B'[Date] )
    ),
    "Output",
    VAR d = [Date]
    Var A = [Account]
    VAR initValue =
        CALCULATE (
            SUM ( 'Table A'[Amount] ),
            'Table A'[Account] = EARLIER ( [Account] )
        )
    RETURN
        SWITCH (
            [Value],
            "Rate1",
            VAR r1 =
                PRODUCTX ( FILTER ( 'Table B', [Date] <= d && [Account] = A ), 1 - [Rate1] )
            VAR r2 =
                PRODUCTX ( FILTER ( 'Table C', [Date] < d && [Account] = A), 1 - [Rate2] )
            RETURN
                IF ( r1 = 0, 1, r1 )
                    * IF ( r2 = 0, 1, r2 ) * initValue,
            "Rate2",
            VAR r1 =
                PRODUCTX ( FILTER ( 'Table B', [Date] <= d && [Account] = A), 1 - [Rate1] )
            VAR r2 =
                PRODUCTX ( FILTER ( 'Table C', [Date] <= d&& [Account] = A ), 1 - [Rate2] )
            RETURN
                IF ( r1 = 0, 1, r1 )
                    * IF ( r2 = 0, 1, r2 ) * initValue
        )
)

Best regards,

[Anonymous]

Thank you!!!  This works exactly as i need it to, i confess i don't understand everything, but it works!  If you could provide a little bit of a breakdown of how the code works i would greatly appreciate it. Kudos to you!

[Anonymous]

Thanks, let me test this out to see if it works, if so i will mark it as a solution! Thank you!

[amitchandak]

Typically such scenario needs to be achieved using cumulative. It may be a total of one or addition subtraction of few

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Cumm Balance = CALCULATE(SUM(purchase[purchase Amount]),filter(purchase,purchase[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin