Rolling number of invoice not yet due

[CarlBlunck]

Hi team,

I am trying to calculate the rolling number of invoices that are not yet due.  For example, if an invoice was posted on the 01/01/2023 and has a due date of 30/06/2023, and I have a chart that shows the month-year from Jan 23 - Jul 23.  Then I would expect this to show a count of 1 for the months of Jan 23 - Jun 23 only.  Nothing for Jul 23, cause it is past it's due date.

I have tried this formula, but not getting the result and am unsure what other method to try...

From a model perspective, the d365_finops_vendtrans[DueDate] creates the dates in the dim_VendTrans-DueDateCalendar' table and the d365_finops_vendtrans[DueDate] is connected to the 'dim_VendTrans-DueDateCalendar'[Date] column.

Rolling Number of NYD DF Invoices =

VAR EOM = Max('dim_VendTrans-DueDateCalendar'[Date])

RETURN

    CALCULATE(

        [Number of DF Invoices],

        d365_finops_vendtrans[DueDate] < EOM,

        d365_finops_vendtrans[PostingDate] <EOM,

        ALL ('dim_VendTrans-DueDateCalendar'[Date] )

    )

[DallasBaba · Accepted solution]

@CarlBlunck this should give you the count of invoices that are not yet due for each month in your date range

Rolling Number of NYD DF Invoices =
VAR EOM = Max('dim_VendTrans-DueDateCalendar'[Date])
RETURN
    CALCULATE(
        [Number of DF Invoices],
        FILTER(
            ALL('dim_VendTrans-DueDateCalendar'[Date]),
            'dim_VendTrans-DueDateCalendar'[Date] <= EOM
        ),
        FILTER(
            ALL(d365_finops_vendtrans),
            d365_finops_vendtrans[DueDate] > EOM
        )
    )

 Or

Rolling Number of NYD DF Invoices =
VAR EOM = Max('dim_VendTrans-DueDateCalendar'[Date])
RETURN
    CALCULATE(
        [Number of DF Invoices],
        d365_finops_vendtrans[DueDate] >= DATE(2023,1,1),
        d365_finops_vendtrans[DueDate] <= EOM,
        d365_finops_vendtrans[PostingDate] < EOM,
        ALL ('dim_VendTrans-DueDateCalendar'[Date])
    )

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Let me know if this work
@ me in replies or I'll lose your thread!!!

Thanks