How to Recreate Monthly History

[Txtcher]

FYI: I am using Excel, not Power BI.

I have a table in Power Query that includes the following date columns:

Due By

Entrance Date.

I also have a Date Table with columns for Date, Fiscal Year, Fiscal Month, Fiscal Quarter, Month, Year.

I want to create a power pivot that shows a history of overdue records for the past year by month.  The question I am trying to answer is:  For each month in the prior year, how many records were overdue as of the 1st of the month? For example, On February 1st, how many records were overdue in Jan 2024?

The criteria would be:

Due Date <=2/1/2024

Entrance Date =null

OR Entrance Date >Due Date AND >Jan 2024

I can't get my head around how to build a table that will generate a monthly look back with a power pivot. 

Would I need to create a table with Due By and Entrance Columns and then custom columns for each of the 12 months with an If statement that produced Overdue, not overdue?

And then how do I make it dynamic... How to create it so that it always looks back over the current year? 

[bhanu_gautam]

@Txtcher , Try using below method

Ensure your Date Table has all necessary columns. You can create a Date Table in Power Query or use an existing one.

Add custom columns to determine if a record is overdue for each month. Here is an example for January 2024:

let
Source = YourTable,
AddCustomColumn = Table.AddColumn(Source, "Overdue_Jan_2024", each if [Due By] <= #date(2024, 2, 1) and ([Entrance Date] = null or ([Entrance Date] > [Due By] and [Entrance Date] > #date(2024, 1, 31))) then "Overdue" else "Not Overdue")

Repeat this for each month you want to analyze.

Load your transformed data into Power Pivot.

Create measures to count the overdue records for each month. Here is an example for January 2024:

Overdue_Jan_2024_Count :=
CALCULATE(
COUNTROWS(YourTable),
YourTable[Overdue_Jan_2024] = "Overdue"
)
Repeat this for each month.

Create a Pivot Table in Excel using the measures you created. Add the Date Table's month column to the rows and the overdue count measures to the values.

To make the analysis dynamic and always look back over the current year, you can use DAX to create dynamic measures. Here is an example for a dynamic overdue count measure:

Overdue_Count :=
VAR CurrentMonth = MAX(DateTable[Month])
VAR CurrentYear = MAX(DateTable[Year])
RETURN
CALCULATE(
COUNTROWS(YourTable),
YourTable[Due By] <= EOMONTH(TODAY(), -1) + 1,
ISBLANK(YourTable[Entrance Date]) ||
This measure dynamically calculates the overdue count based on the current date.

[Txtcher]

Thank you very much for the thorough response. 

I am very much a newbie with respect to DAX. I am having trouble with the expression to make the backlog count dynamic.  The message I am receiving states the expression is incomplete. Is there something that should come after || (means OR, yes?)

ETA: I did not see how the variables served any purpose. Please let me know if I need them and if so, how are they referenced in the expression. 

I think the DAX expression has to read Countrows when:

Due By is < the 1st of the current month and Entrance Date is null

OR

Due By is between 1st day of prior month and last day of the the prior month and Entrance Date is > Due By.

But, I don't know how to write that and so far my attempts result in an error. 

[Anonymous]

Hi @Txtcher ,

Thanks for bhanu_gautam's reply!
You'd better provide us with some sample data to clearly describe your needs. I'm not sure if we understand your meaning correctly. From your description, I think you may want to calculate how many records are overdue in the previous month of each month, but the condition you mentioned makes me doubt:

Due By is < the 1st of the current month and Entrance Date is null

If Due by is 1/5/2024 and Entrance Date is null, then this record is an overdue record for February 1st, and also an overdue record for March, April, etc. This conflicts with my previous understanding.

Did I understand it wrong or do you have some records that are overdue for multiple months at the same time?

Best Regards,
Dino Tao