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
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!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        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.