Forum Discussion
Dynamically Incrementing Month Index
Hi everyone,
Hoping someone can help me with something that I thought would be easier to do!
I have a matrix that shows values on Rows with Fiscal periods as columns and a bunch of financial metrics as rows. For one particular metric, DSO (Day Sales Outstanding), I have a requirement to calculate it differently if I do not have 12 months of data for the particular Project Manager being filtered.
My normal DSO formula is [AR Running Total] / [Revenue] * 365
This works fine but if the currently filtered PM only has a couple of months of data this formula will unfairly caculate the DSO very high. So I found a way to look back at Revenue 12 months prior and if there is none, use this formula instead:
[AR Running Total] / ([Revenue] / CountOfMonths) * 12) * 365
So now where I'm stuck....getting the count of months with data. My date table has an Index column and I can successfully have my matrix ignore months where there is no revenue. Below is a screenshot of my attempt at getting the month count. I am able to show the correct Month Index from my date table, however I need it to reset to 1 instead of 192. This way in each column I can insert that value for the "CountOfMonths" shown in the above formula. I have tried using MIN of the Index but this will give me 182 which is the first month that is selected in my slicer - typically we select a 12 month range, but if there is no data I'm not showing those empty columns. Even if they were visible, I would still need the first month with data to be 1, second to be 2, etc. so that I can use that in the DSO formula.
I thought there would be a much simpler way to identify the first column with data but I'm stumped!
Thanks in advance for any sage advice!
Kevin Coles
14 Replies
I just want to make sure its clear about what I need. Essentially I need to know in each month column, the count of months, where data is present (specifically where the Revenue > 0). So in the screen shot where you see 192, 193, that is the index number of that month in my month Calendar.
I need to identify that December is Month 1 and January is Month 2, because they have Revenue. So if someone selects 12 months, but only those last 2 have Revenue, I need to calculate my DSO based on the cumulative Number of Months in that column, e.g. 1 month in Dec., 2 months in January.
I hope that makes the requirement clearer!
KevinHi Caiyun,
I'm unable to provide data as it is sensitive and it is a very large pbix file.
What I'm wanting should be generic enough. I need to know the current column count of each column where a specific measure (in this case revenue) is greater than 0.So given someone selects 12 months on the slicer, but revenue is 0 until month 9, that should be a count of 1 month, then month 10 would be a count of 2 months, and so on. My date table has an index column but I need to know a simple count of months with Revenue in the current column. I've shown this below. I will probably need to accomodate a $0 month in between as well but for now this is about a new Project Manager who would have no Revenue until Month 9 so I need to calculate his DSO based on an averaging of the Revenue he does have, hence the need for the month count.
Thanks,Hi,
I am interested in helping. Can you share a small anonymised PBI file and show the expected result there.
Sorry forgot to post the link:
Maybe something like this:
DSO =
VAR _CountOfMonthsWithSales =
FILTER ( VALUES ( Calendar[MonthInYear] ), [Revenue] > 0 )
RETURN
DIVIDE (
[AR Running Total],
DIVIDE ( [Revenue], _CountOfMonthsWithSales ) * 12
) * 365Hi bdarbo78,
In your example is Calendar[MonthInYear] intended to be the month number within the year (e.g. December = 12, January = 1)?
If so, this is not what I'm trying to achieve. I don't want to know the position of the month, but rather a count of months with revenue up to that position. E.g. if December is the first month with Revenue, then there is a count of 1 month with Revenue, if January is the second month with Revenue, then there is a count of 2 months up to January with Revenue.
Thanks,Kevin
