Please attach your sample data as twbx format.
Hi Mark Alvarado,
Do you mean you want the line to be previous week? Meaning the line could pass the circle? Or are you planning to do a lollipop graph with the line either going up or down based on performance from the previous n weeks?
Regardless, I think you could double the number of weeks the user selects for your date filter and then flag each set using a simple IF-ELSEIF Statement.
Here are a couple calculations I made:
DATE(DATEADD("week", -[PA_NWeeks]+1, DATETRUNC("week", [CF_ReportWeekEnd])))
DATE(DATEADD("week", -[PA_NWeeks]*2+1, DATETRUNC("week", [CF_ReportWeekEnd])))
IF [Date] >= [CF_PeriodStart_Previous]
AND [Date] < [CF_PeriodStart_Current] THEN "Previous"
ELSEIF [Date] >= [CF_PeriodStart_Current]
AND [Date] <= [CF_ReportWeekEnd] THEN "Current"
This might get you started. If you're doing a lollipop chart with difference from previous then you can group the dates using similar fashion (essentially changing the "flag" to a date rather than a string).
Gotcha. I suppose I should have called it a dumbbell chart instead! How's this?
You should have CNT(OSD Reason) on your rows measures twice.
Make one a circle:
- Size: period flag
- Color: OSD
Make the other a line:
- Path: period flag
Hopefully this helps!
Dumbbell.twbx 14.9 KB
Yep - you'll have to get rid of those unfortunately! But imagine you can take some of the logic to make what I called CF_Period_Flag in a previous post. Essentially you just need to flag dates as either "Current" or "Previous" so you can use these in the pane.
Here's my two cards:
You can imagine it's a new measure with the code CNT([OSD Reason]) instead of SUM([Incidents]) for the measures.
1 of 1 people found this helpful
Hi Mark Alvarado,
So, my suggestion would be to follow the steps from above: Re: Date parameter and/or date calculations??
Instead of going straight to assigning [OSD Reason] based on date range, I'd suggest storing the two date ranges first. I see these say using month when OP said week - I'll use month to tie with what you put here.
Can you convert the Parameter to INTEGER instead of having to do a nested IF? Then you can simply do:
CF_PeriodStart_Current: DATEADD("month", -[Select View], TODAY())
and similar for CF_PeriodStart_Previous:
NOTE: as an aside, if you're using TODAY() as you Report Date for the dashboard, consider truncating that to similar date type ("month", in this case). Otherwise you'll have some months that are both "Current" and "Previous".
If you truncate it, then you should add 1 to your DATEADD so you're not going back too far, assuming you want the current month as part of the current period:
The Flag is then just using these two fields in a Date Comparison with your relevant Date:
Lastly, your filter for the sheet can be this field:
Now you shouldn't have to null out the OSD Reason. Simply do a COUNT([OSD Reason]) with the views shared previously as the filter and items on the marks cards should take care of things appropriately.
Perfect! One suggestion is to switch the two marks in the measures pane - this would put the line behind the two circle points, which might look nicer and make labels easier to read.
Also, you could test making a new calculated field for the labels using CHAR(10), but maybe a little excessive. Heh.
In truth, you might actually need to do a comparison calc instead and add the CHAR(10)s to whichever circle is LESS as to not have both labels in between the circles. Heh. I tried doing so with the following:
Best of luck!