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

Dynamic Date Filter for Sum

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?

5 REPLIES 5
Microsoft
Microsoft

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

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

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?

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

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

you can try this:

 

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

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors