Forum Discussion

KevinColes's avatar
KevinColes
Icon for Helper III rankHelper III
5 years ago

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 financia...
Need Help
v-cazheng-msft's avatar
v-cazheng-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi KevinColes 

Can you provide some sample data removing sensitive data?

 

Best Regards

Caiyun Zheng 

KevinColes's avatar
KevinColes
Icon for Helper III rankHelper III
5 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

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

    • KevinColes's avatar
      KevinColes
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
        KevinColes
        Icon for Helper III rankHelper III
        5 years ago

        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!