Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Exits = COUNTROWS('In-Home')
Help appreciated!
Solved! Go to Solution.
Hi @KeepCalm007,
See if my solution here helps.
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
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?
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 result
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.
Regards
Hi @v-ljerr-msft
really appreciate your help
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?
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.
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 😕
Hi,
I have revised my formula in that file. The download link is the same.
Please check and revert.
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.
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 :
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.
Hi @KeepCalm007,
Sorry for the late response. Based on my test, the formula below should work in your scenario.
Measure = VAR currentMonth = MONTH ( TODAY () ) VAR monthNo = MONTH ( MAX ( 'In-Home'[Closed].[Date] ) ) RETURN IF ( monthNo < currentMonth, [Cumulative To End of Year] )
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |