Dynamic Date Filter for Sum

[thendrikxarvato]

Hi,

I am currently creating an Accounts Receivable Reporting, with a table (among others) for:

- All booked invoices and clearing documents.

I want to check the open amount on a specific date via a filter, for which i created a measure called [DateFilter].

The value of this measure corresponds with the set filter, so this appears to be working correctly.

However, when I try to create a measure for calculate the amount open on [DateFilter], I get a blank result.

My current expression for the open amount measure is as follows:

CALCULATE (SUM (TableInvoices[Amount]; FILTER (TableInvoices ; TableInvoices[ClearingDate] <=DateFilter && TableInvoices[Net due date] < DateFilter))

Could someone please assist me with creating the correct measure?

[v-yulgu-msft]

Hi @thendrikxarvato,

How did you create the measure [DateFilter]? To calculated the amount whose clearing date is before the selected date in slicer, you should create an extra calendar table which is unrelated to your source table. Then, add date column from calendar into slicer, create DateFilter=Max('CaendarTableName'[Dates])

Best regards,

Yuliana Gu

[thendrikxarvato]

Hi @v-yulgu-msft,

I already have a separate table in place with dates, starting with January 1st 2015 and ending on December 31st 2019.

Is used the MIN() function instead of the MAX() function, should that make a difference?

[v-yulgu-msft]

Hi @thendrikxarvato,

Using MIN() or MAX() doesn't make any difference.

Please replace ALL() with ALLSELECTED for a test:

=
CALCULATE (
    SUM ( TableInvoices[Amount] );
    FILTER (
        ALLSELECTED ( TableInvoices );
        TableInvoices[ClearingDate] <= DateFilter
            && TableInvoices[Net due date] < DateFilter
    )
)

Best regards,

Yuliana Gu

[mattbrice]

you can try this:

=
CALCULATE (
    SUM ( TableInvoices[Amount] );
    FILTER (
        ALL ( TableInvoices );
        TableInvoices[ClearingDate] <= DateFilter
            && TableInvoices[Net due date] < DateFilter
    )
)

[thendrikxarvato]

Hi @mattbrice

With this set-up, I get a result but unfortunately, the result does not change when the filter changes.

When I try to use ALLEXCEPT on the column containing the amount, the result also does not change when the filter changes.