Dynamically Incrementing Month Index

[KevinColes]

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

[bdarbo78]

Maybe something like this:
DSO =
        VAR _CountOfMonthsWithSales =
                 FILTER ( VALUES ( Calendar[MonthInYear] ), [Revenue] > 0 )
        RETURN
          DIVIDE (
                [AR Running Total],
                DIVIDE ( [Revenue], _CountOfMonthsWithSales ) * 12
          ) * 365

[KevinColes]

Hi @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

[KevinColes]

Sorry forgot to post the link:

https://klcoles-my.sharepoint.com/:f:/p/kevin/Eh-HwNKJpstIsoTX7m834-gBcHe_cyusP65Dopgq-YjQTw?e=fooUa...

[v-cazheng-msft]

Hi @KevinColes 

Can you provide some sample data removing sensitive data?

Best Regards

Caiyun Zheng 

[KevinColes]

Hi 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,

[Ashish_Mathur]

Hi,

I am interested in helping.  Can you share a small anonymised PBI file and show the expected result there.

[KevinColes]

Hi Ashish,

Sorry for the delayed repsonse. I will try to put together something in a separate file to simulate this and upload it.

Thanks!

[KevinColes]

Hi Ashish,

I've attached a stripped down verison of my pbix. Unfortuately it would take a very long time to completely anonymize it and I have a lot of measures so I ended up leaving all of them in although I stripped many tables out and filtered down to 2 Project Managers to show the issue.

In the sample I have Feb 2020 through Jan 2021 selected (12 months).

If you toggle between the 2 Project Managers you will see one has 12+ months of data going back and so his DSO calculates appropriately. For the other, he only has revenue data for Dec-2020 / Jan-2021 because he is new. So I need to use a different method to calculate his DSO which involves having the current month's count of months with Revenue. December being Month 1 and January being month 2.

I have the logic for determining when data exists 12 months prior to the month and I can do the If/Else logic for that. The only missing piece I have is that I do not know how to determine that December is the first month with Revenue and January is the second month with revenue. 

I have to assume that the same time period will be selected when toggling between PMs and therefore need to know that month count to get a fairly calculated DSO for a new PM.

Let me know if you have questions.

Thanks!

[Ashish_Mathur]

Hi,

You have not built any relationship to the Date column of the Fiscal Period table.  This is the only column of the Fiscal Period table which should have a relationship with your other tables.  That is absolutely necessary and will enable use to use the Date Intelligence functions.  Please do that and share the link of the revised file. 

[KevinColes]

Hi Ashish,

There are relationships between my Fiscal Periods (Date Table) and the AR90180, Unbilled and KPI tables. This project is actually 25 different reports and all rely on the relationship between Fiscal Period. My reports are displayed as Month Year (e.g. Jan - 2021) but this is all based on the Fiscal period (E.g. Jan. is 202104). The company uses a Fiscal accounting year that begins in October.

I do not have any field in my fact tables for date as all of my data comes out of an ERP system based on Fiscal Accounting period, not by date.

Thanks,

Kevin

[Ashish_Mathur]

Hi,

I was hoping that this measure would work but i get 1 for both months instead of getting 1 and 2.  May be you can try from here.

Measure = COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE('Fiscal Periods',DATESBETWEEN('Fiscal Periods'[date],MINX(ALL('Fiscal Periods'),'Fiscal Periods'[date]),max('Fiscal Periods'[date]))),'Fiscal Periods'[MonthYear],"ABCD",[Total Revenue]),[ABCD]>0))

[Ashish_Mathur]

Hi,

For me to solve the problem, I will need a proper date column.  Even if do not have one but instead have just Year and Month, I can always create a date column from the 2 columns.

[KevinColes]

Hi,

I'm not sure why the existing relationships don't work, can you explain further? I'm not trying to do any sort of date math at all here. I simply need to know a count of months with data. The Fiscal Periods table has a Period Column, a Month Year Column, month,  year, etc. 

In any case, you have the file so you can manipulate it any way you see fit if you think you can solve the problem. Then I can adapt that into my live file. I just don't see why a date column is required. I do not have any individual dates in my fact tables. Everything is based on Period. 

Thanks,

Kevin

[KevinColes]

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!

Kevin