I'm putting together a running total/count based on how old a loan is, based on months. This column is called CO_Month. For my x-axis, I'm aggregating these totals based on the year they originate. Here are the measures I've created:
CountCOLoans = COUNT('VW_Chargeoffs'[InstrumentInd])
(I know this measure could be used as a VAR in the next measure, but I use it elsewhere too)
CountCOLoans_RunningTotal =
CALCULATE(
[CountCOLoans],
ALLSELECTED(VW_Chargeoffs[CO_Month]),
'CO_Months'[CO_Month] <= MAX('CO_Months'[CO_Month])
)
A calculated table, 'CO_Months,' is used to fill in the blanks where data is missing or unavailable for the CO_Month field.
CO_Months =
FILTER(
VALUES('VW_Chargeoffs'[CO_Month]),
'VW_Chargeoffs'[CO_Month] <=
DATEDIFF(DATE(2022,12,31),EOMONTH(TODAY(),-1),MONTH)-1
)
However, I am off where I created my calculated table because I have no values for 0-3 (where there is no data), and I have unnecessary data/graph lines where I would not expect them to be for the 2024 vintage year. The data should stop for 2024 at the 14-month mark (based on the data set I have now).
What data/graph looks like in the current described set-up
What data should look like (Excel built)
I've puzzled over this for quite some time and scoured numerous forums/help sites, but I've been unsuccessful. In fact, The idea for building the calculated table came from my research. I'd like some wisdom and better minds to help me match what I was able to build out in Excel before moving this into Power BI. Thank you!
I found a workaround (though I'm not sure if this was the best practices approach) that solved the main issue/frustration: getting my data to stop graphing when there was no data from a data point going forward. I created a new calculated column in my calculated table that re-assigned numbers for CO_Months based on year.
This data table snapshot visually shows what my new calculated column would be doing. Here is the column formula that accomplished this:
CO_Month_Reorder = MAX(
CO_Months[CO_Months] +
IF(CO_Months[VintageYear_CO_Months]=2023, 0,
((CO_Months[VintageYear_CO_Months]-2022)*12) +
((MONTH(EOMONTH(TODAY(),-1))-1) *
(CO_Months[VintageYear_CO_Months] - 2023))),
0)
I then appended the earlier-mentioned measure as such:
CountCOLoans_RunningTotalY =
CALCULATE(
[CountCOLoans],
ALLSELECTED('VW_Chargeoffs'[CO_Month]),
'CO_Months'[CO_Months] <= MAX('CO_Months'[CO_Months]),
'CO_Months'[CO_Month_Reorder] <= MAX(CO_Months[CO_Month_Reorder])
)
I now am left with the easier task of solving for no values for CO_Months 0-3 where there is no data.