Forum Discussion

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

    • KeepCalm007's avatar
      KeepCalm007
      Helper I
      8 years ago

      Greetings v-ljerr-msft, your formula was working ..

      .. but stopped working after  I downloaded the latest update of power bi desktop :(

       

      I haven't made any changes to the measures, so am unsure why things are behaving differently.

       

       

      Is it an issue with the other measures? But I can't think what is wrong with them if they were working before :/

       

      Cumulative To End of Year =
      CALCULATE (
      [Exits],
      FILTER ( ALLEXCEPT ( 'Closed Cases', 'Closed Cases'[Service Type], 'Closed Cases'[Service], 'Closed Cases'[Outcome]),'Closed Cases'[Closed].[Date] <= MAX ( 'Closed Cases'[Closed].[Date])))

       

       

      Exits = COUNTROWS('Closed Cases')

       

      Again, I appreciate any advice or pointers! I didn't think I would have such difficulty.

      update: file

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi KeepCalm007,

     

    The graphs appear just fine for me.  I just downloaded your PBIX file and this is what i got.  Where's the problem?

     

     

     

    • KeepCalm007's avatar
      KeepCalm007
      Helper I
      8 years ago

      Hi Ashish_Mathur , 
      apologies, that is the old version of my PBIX file. The problem I was experiencing before, you can see in August column, for 'Lower North' , the grey area in July did not carry through to August. - it is supposed to be cumulative. So the August column should also show grey data from July.

       

      That was fixed with the help of v-ljerr-msft 's formula, until I downloaded the latest version of Power BI desktop, and things stopped working. Not sure why

      Now all the months show cumulative result to date - instead of to the month itself - but no measures had been altered.

      This is the current PBIX file - If I can fix it on the main graph, I can surely duplicate the method to the other smaller graphs.