Aggregated Result based on Date Slicer

[mussaenda]

Hi,

If someone can help me, please.

I have these data for the advance payment:

Date Created      Date Received     Customer No    Control No       Debit     Credit       Amount

 31/01/2019         25/01/2019             C001              D19-001                       -1000         -1000       line1

 05/02/2019         25/01/2019             C001              D19-001          1000                           0           line2

 04/02/2019         30/01/2019             C002              D19-002                        -2000        -2000       line3

 08/02/2019         30/01/2019             C002              D19-002          2000                           0           line4

 01/03/2019         15/02/2019             C005              D19-003                        -3000        -3000       line5

Date Created: When we post the data

Date Received: When we receive the data

Customer No: Unique No for each customer

Control No: Document Series No

Debit: When we put it to the bank

Credit: When we received the the payment

Amount: On hand Total

I am required to show the following:

I have the selection of Date created (between)

Date Selection: 31/01/2019 - 04/02/2019

Expected Result:

Date Created      Date Received     Customer No    Control No         Amount

 31/01/2019         25/01/2019             C001              D19-001            -1000            line1

Date Selection: 31/01/2019 - 05/02/2019

Expected Result:*

Date Created      Date Received     Customer No    Control No            Amount

 04/02/2019         30/01/2019             C002              D19-002                -2000        line3

*The result is not showing line2 because we already sent it to the bank based on created date so it means it is not considered as advance anymore.

Date Selection: 31/01/2019 - 15/02/2019

Expected Result:**

Date Created      Date Received     Customer No    Control No            Amount

**Nothing shows here because we deposited all the advance payment to the bank based on the selection of the date

Date Selection: 31/01/2019 - 31/03/2019

Expected Result:***

Date Created      Date Received     Customer No    Control No            Amount

01/03/2019         15/02/2019             C005              D19-003                 -3000       line5

***Only this will show because this payment has no data yet that we sent it to the bank

Please let me know if there is any way. I have been stuck here for weeks now.

Your suggestions and detailed instructions will be highly appreciated.

Thank you,

Mia

[v-shex-msft]

HI @mussaenda ,

You can write a measure with all function to ignore current filter effect, then use selected value to filter whole table to do aggregated result.

Measure =
VAR selected =
    ALLSELECTED ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Credit] ),
        FILTER (
            ALL ( Payment ),
            [Date created] >= MINX ( selected, [Date] )
                && [Date created] <= MAXX ( selected, [Date] )
        )
    )

Using ALLEXCEPT versus ALL and VALUES

Regards,

Xiaoxin Sheng

[mussaenda]

Sorry, I'm lost somewhere. If you can provide me a sample pbix. This will be a great help. Thank you

[mussaenda]

Thank you so much @v-shex-msft!

The Date we used for the VAR, is from?

I tried to use the creation date for this and it returns same total value for the same creation date

Thank you

[v-shex-msft · Accepted solution]

Hi @mussaenda ,

The variable 'selected' is from 'date selection', please modify it to date table which you used to filter current table.
If you still confused how to use it, please share a pbix file with sample data for test.

Regards,

Xiaoxin Sheng