subtraction with two coloumns and project in time

[Anonymous]

hi community, I'm asking for your help ; I have in my dataset four columns Asset ID, Date, TotalAmount and SubtractionByMonth. I wish to make a total subtraction of TotalAmount by deducing the column SubtractionByMonth according to the 18 following months and to display each month with the remainder to deduct in columns even if there is nothing to deduct before turning off the 18 months, in this cases display Zeros until the 18th month. Thanks in advance.

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Reminder = 
VAR currentDate =
    MAX ( 'Table'[Date] )
VAR AID =
    DISTINCT ( 'Table'[Asset ID] )
VAR t =
    ALLSELECTED ( 'Table' )
RETURN
    IF (
        DATEDIFF (
            CALCULATE (
                MIN ( 'Table'[Date] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Asset ID] IN DISTINCT ( 'Table'[Asset ID] )
            ),
            currentDate,
            MONTH
        ) > 18,
        BLANK (),
        IF (
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        FILTER ( t, [Date] <= currentDate ),
                        'Table'[Date].[Year],
                        'Table'[Date].[MonthNo],
                        "Temp",
                        VAR td =
                            DATE ( [Date].[Year], [Date].[MonthNo] + 1, 1 ) - 1
                        RETURN
                            CALCULATE (
                                CALCULATE (
                                    SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
                                    t,
                                    'Table'[Date] <= td,
                                    'Table'[Asset ID] IN AID
                                )
                            )
                    ),
                    [Temp] <= 0
                )
            ) > 0,
            0,
            CALCULATE (
                SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Date] <= currentDate,
                'Table'[Asset ID] IN AID
            )
        )
    )

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?

By the way, PBIX file as attached.

Best regards,

[Anonymous]

I still haven't found the solution, do you have an idea ?

@v-lid-msft 

@nandukrishnavs 

[v-lid-msft]

Hi @Anonymous ,

The table looks as similar as we have shared before, how about the result after you follow the suggestions mentioned in my original post? Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Anonymous]

Hello , your proposal is good but the need that it's was asked for me is to display the months in column format no rows, but thank you for your effort in any case. calculate it is good.

But i still have some issues to how adapt with my own data.

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my previous post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calendar table first (you can use yours)

CalendarTable = ADDCOLUMNS(CALENDARAUTO(),"MonthYear",FORMAT([Date],"YYYYMM"))

Then modify the measure as following:

Reminder = 
VAR currentDate =
    MAX ( 'CalendarTable'[Date] )
VAR AID =
    DISTINCT ( 'Table'[Asset ID] )
VAR t =
    ALLSELECTED ( 'CalendarTable' )
RETURN
    IF (
        DATEDIFF (
            CALCULATE (
                MIN ( 'CalendarTable'[Date] ),
                ALLSELECTED ( 'CalendarTable' ),
                'Table'[Asset ID] IN DISTINCT ( 'Table'[Asset ID] )
            ),
            currentDate,
            MONTH
        ) > 17,
        BLANK (),
        IF (
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        FILTER ( t, [Date] <= currentDate ),
                        'CalendarTable'[Date].[Year],
                       'CalendarTable'[Date].[MonthNo],
                        "Temp",
                        VAR td =
                            DATE ( [Date].[Year], [Date].[MonthNo] + 1, 1 ) - 1
                        RETURN
                            CALCULATE (
                                CALCULATE (
                                    SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
                                    t,
                                    'Table'[Date] <= td,
                                    'Table'[Asset ID] IN AID
                                )
                            )
                    ),
                    [Temp] <= 0
                )
            ) > 0,
            0,
            CALCULATE (
                SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Date] <= currentDate,
                'Table'[Asset ID] IN AID
            )
        )
    )

By the way, PBIX file as attached.

Best regards,

[nandukrishnavs]

@Anonymous 

Can you share your sample table and expected output in a tabular format.