Forum Discussion

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi KeepCalm007,

 

Could you try using the formula below to create a new measure which refers your [Comulative to Last Month] measure to see if it works? :smileyhappy:

 

New Measure =
VAR currentMonth =
    MONTH ( today )
VAR monthNo =
    MONTH ( MAX ( 'In-Home'[Closed] ) )
RETURN
    IF ( monthNo < currentMonth, [Comulative to Last Month], BLANK () )

 

Regards

KeepCalm007's avatar
KeepCalm007
Helper I
8 years ago

Thank you for this suggestion, although it looked very promising,

I have tried it with both of the cumulative measures, but they do not give a different result:smileyfrustrated:

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi KeepCalm007,

     

    Could you share a sample pbix file which can reproduce the issue? So that we can further assist on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

     

    Regards

    • KeepCalm007's avatar
      KeepCalm007
      Helper I
      8 years ago

      Hi v-ljerr-msft 
      really appreciate your help

       

      pbix file here

       

      What I am trying to achieve are separate graphs for each 'Service', showing a cumulative number of exits from those services for the coming months, color-coded by service outcome so we can see the proportions

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi KeepCalm007,

         

        Sorry for the late response. Based on my test, the formula below should work in your scenario. :smileyhappy:

        Measure = 
VAR currentMonth =
    MONTH ( TODAY () )
VAR monthNo =
    MONTH ( MAX ( 'In-Home'[Closed].[Date] ) )
RETURN
    IF ( monthNo < currentMonth, [Cumulative To End of Year] )

         

        Regards