cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

subtraction with two coloumns and project in time

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: subtraction with two coloumns and project in time

Hi @slakhdin ,

 

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
            )
        )
    )

 

1.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Community Champion
Community Champion

Re: subtraction with two coloumns and project in time

@slakhdin 

 

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

Highlighted
Community Support
Community Support

Re: subtraction with two coloumns and project in time

Hi @slakhdin ,

 

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
            )
        )
    )

 

3.jpg

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: subtraction with two coloumns and project in time

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

@v-lid-msft 

@nandukrishnavs 

Highlighted
Community Support
Community Support

Re: subtraction with two coloumns and project in time

Hi @slakhdin ,

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: subtraction with two coloumns and project in time

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.

Highlighted
Community Support
Community Support

Re: subtraction with two coloumns and project in time

Hi @slakhdin ,

 

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
            )
        )
    )

 

1.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support
Community Support

Re: subtraction with two coloumns and project in time

Hi @slakhdin ,

 

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!