DAX calculate filter date lower than today

[rsanyoto]

Hi guys,

I have this DAX Measure but i need to add one more filter in order to get results of all balance due invoice where DueDate is smaller than today.

Balance Due Invoice Euro = CALCULATE(SUM(Fact_CustLedgerEntryDetailed[Amount]),

                            FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[EntryStatus] = "Open"),

                            FILTER(Fact_CustLedgerEntry, Fact_CustLedgerEntry[DescriptionType] = "Order"),

                             FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[CustomerPostingGroup] = "BIN"),

                           Fact_CustLedgerEntry[DueDate] < Fact_CustLedgerEntry[Today]

                              )

Unfortunatly this measure doestn work. Any suggestions?

Many thanks

[Adescrit]

Hi @rsanyoto ,

You could use the built in DAX function for today's date - TODAY().

See DAX below:

Balance Due Invoice Euro = CALCULATE(SUM(Fact_CustLedgerEntryDetailed[Amount]),

                            FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[EntryStatus] = "Open"),

                            FILTER(Fact_CustLedgerEntry, Fact_CustLedgerEntry[DescriptionType] = "Order"),

                             FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[CustomerPostingGroup] = "BIN"),

                           FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[DueDate] < TODAY())

                              )

Remember to ensure your due date column is formatted as a Date or Date/Time.

It's also good practice to create a CALENDAR table containing a list of dates. You can then create a relationship between that and the DueDate, and filter via the Calendar table. The DAX for that would look like so:

Balance Due Invoice Euro = CALCULATE(SUM(Fact_CustLedgerEntryDetailed[Amount]),

                            FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[EntryStatus] = "Open"),

                            FILTER(Fact_CustLedgerEntry, Fact_CustLedgerEntry[DescriptionType] = "Order"),

                             FILTER(Fact_CustLedgerEntry,Fact_CustLedgerEntry[CustomerPostingGroup] = "BIN"),

                           FILTER(CALENDAR,CALENDAR[Date] < TODAY())

                              )

[parry2k · Accepted solution]

@rsanyoto try following and if doesn't explain what is not working, error or getting the wrong result if the wrong result then share sample data and expected output

Balance Due Invoice Euro = 
CALCULATE(SUM(Fact_CustLedgerEntryDetailed[Amount]),
Fact_CustLedgerEntry[EntryStatus] = "Open",
Fact_CustLedgerEntry[DescriptionType] = "Order",
Fact_CustLedgerEntry[CustomerPostingGroup] = "BIN",
Fact_CustLedgerEntry[DueDate] < TODAY()
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡