topic Re: Rolling number of invoice not yet due in DAX Commands and Tips

Rolling number of invoice not yet due

CarlBlunck — Wed, 18 Oct 2023 01:39:48 GMT

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] )

)

Re: Rolling number of invoice not yet due

DallasBaba — Wed, 18 Oct 2023 02:48:21 GMT

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 ) )

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