rolling count
1 Topic
Rolling number of invoice not yet due
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] ) )Solved433Views0likes1Comment