Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KeepCalm007
Helper I
Helper I

Cumulative Graph Troubles

Hello

 

Trying to make a cumulative graph that shows data up till the last month, leaving the future months blank,

however ran into trouble of disappearing data The one 'grey' in July should be visible in August as well. The green is correct.

I have checked my filters and data to make sure there is no issue there, so it must be my formula calculation.

 

Capture1.PNG

 

August is finally correct when I make a modification, however, I want the graphs to stop at August, not show anything for the future months. Please help!

Capture 2.PNG

 

Exits = COUNTROWS('In-Home')

 

Help appreciated!

1 ACCEPTED SOLUTION

Hi @KeepCalm007,

 

See if my solution here helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Hi, not sure if you did had the solution but to remove the futur months use the suggestion from @Ashish_Mathur but CONCATENATE the dates:

 

Measure =
VAR currentMonth =
    CONCATENATE(YEAR(TODAY()), FORMAT(TODAY(), "MM"))
VAR monthNo =
    FORMAT( MAX('T_BASS_DIM_DATE (Jobs)'[Date]),"YYYYMM" )
RETURN
    IF ( monthNo <= currentMonth, [Cumulative To End of Year], BLANK() )

 

Regards @KeepCalm007

@Anonymous

v-ljerr-msft
Employee
Employee

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? Smiley Happy

 

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

 

Regards

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 resultSmiley Frustrated

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. Smiley Happy

 

Regards

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

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?

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi @KeepCalm007,

 

Check my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur

Thank you very much for having a look. Your measure looks very clever, however this is looking similar to the original problem - which was that the light grey does not appear as it should on the last cumulative column. The September column is supposed to show 'Cases closed' from July to September inclusive. It's quite puzzling why the data does not show as one would expect 😕

 

 

Capture2.PNG

Hi,

 

I have revised my formula in that file.  The download link is the same.

 

Please check and revert.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Greetings @Ashish_Mathur

 

The light grey is now showing on the last month! 🙂

 

However, the future months in the year do not show on the x-axis anymore. Is this the only way the formula is able to work? 

 

 

Thank you and regards for your help

Hi @KeepCalm007,

 

Check the file now.  Same link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur

Again, thank you so much for your help! This is what I want to achieve regarding the months showing on the x-axis - Showing up to the last data month, leaving future months showing blank :

Graphs.png

Unfortunately the calculations used to achieve this don't work anymore after I downloaded the latest update of power bi desktop - which is the reason for my follow-up post on 10-23-2017 - The post you replied to.

 

If you look at my post on 10-23-2017, that is what happened after refresh.

 

Is it possible for your method to show the future months blank?

 

Regards

Hi @KeepCalm007,

 

See if my solution here helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @KeepCalm007,

 

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

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

r2.PNG

 

Regards

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.

 

Capture1.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.