Work around for dynamic text showing the name of latest month with value > 0?
I have a dataset with data for the latest 14 month. But update time for the various categories in my dataset varies a lot with no fixed schedule. The data source updates daily, but I never know how long it takes before I've got data for all my categories in the past month.
In my KPI I want to show the sum of my categories for the latest month with data for all categories. I know that the category "Sygedagpenge" is the last category to get data. So I can make a calculated column that defines, that if "Sygedagpenge" = 0 in column d-1 then I want to show the total of column d-2 instead of d-1. It works perfectly.
But now I also want to show the text of the month on my KPI with the same "condition". Is there a work around to get the dynamic text to select the name of the month of column d-2 when "Sygedagpenge" in column d-1 = 0?
Maybe I could make a new row that defines 1 for the wanted column and 0 for other columns. And then use the lookup-function? But Im not sure how to use the lookup functionality in this particular case and if it will do the job?
Any good ideas?
Comments
Hi Louise
I have a suggestion that builds on your idea of making a marker/flag that shows where the latest value > 0 of "Sygedagpenge" is available.
If you sum of the date from right to left in the Sygedagpenge row you can set a flag when you reach the point where the value is > 0
The calculation (as a new measure) would look something like this:
if sum(0,@"[Sygedagpenge]",m1)>0 and sum(1,@"[Sygedagpenge]",m1)=0 then 1 else 0
It basically means - if the sum of Sygedagpenge in your current column > 0 and 1 column to the right is = 0 then set the flag to 1 else 0.
This will place a flag in your table in Dec - which is the only month where the Sygedagpenge row meets the condition (the value is > 0 and the value 1 column to the right is = 0).
Now you can hide this calculation with visibility.
You can also make a visibility agent where the condition is:
m2 is your calculated flag - so hide whenever the flag = 0
On the next tab you need to pick the second option - which is to hide a column when the condition is met:
Now you should be left with just one column - which will be the last column where Sygedagpenge has been registered, and the KPI should be easy to set up.
Hope it makes sense :-)
Hi Niels
Thank you for the help, it works now. But the KPI title says 2020 Dec, is there any chance i can get it to show Dec 2020. The hierarchy of the date dimension is (Y,Q,M).
Hi Jakob
I'm glad it worked ::-)
I actually think you can change it.
If you use dynamic captions you can change the lowest level (month level) to show exactly what you want - and then make sure that only this level is used in the header of the KPI.
Here is a table looking a little like yours:
I want to show - not just month names - but month names and year where the month name is shown right now.
I'll right click one of the month captions and select "Edit dynamic captions for members on this level"
The options to change the dynamic caption shows on the left hand side.
I leave cellvalue as is (that means the month names) and add Year from my YQMD hierarchy and click apply
Now my table looks like this (and I might consider hiding the year labels)
When I make a KPI - I choose "new member text" to show in the header
I change the member to my YQMD hierarchy - and here's the result:
Please sign in to leave a comment.