Creating a trended dashboard using different time logic for 2 different measures

#1

HI,

We have run into sort of an issue in one specific scenario. We have a SQL transactional table that contains among several columns a Transaction Date, and 2 measure columns: Cash Collected and Revenue Billed. Goal is to measure Cash Collected v. Revenue Billed but each measure column is using a different period - iow, say you are looking at Cash for March 2020, then Revenue Column on a table/chart should show 1 month lag, so February 2020 in this case. Similarly for a quarter to date comparison, for cash if I am looking at say Q1 2020, that would be Jan-Mar 2020, but for Revenue there is one month lag so QTD for Revenue would be Dec 2019-Feb 2020. Likewise say for YTD 2019 that would be comparison of Jan - Dec 2019 (Cash) v. Jan - Nov 2018 (Revenue).

So the way we accomplished this is by using 2 date filters - one for each measure here (Cash and Revenue). We used Member Value filter (based on Date hierarchy we created (Y-Q-M-D) in conjunction with Slicer for Comparison in a metric set. This works well when we select different months for each measure and do Month-over-Month comparison - i.e. numbers, variances show up correctly. That said, however, we run into issue with “trending” this data, ie showing both measures correctly for say YTD 2019 with months on the X-axis - NOT sure how to accomplish this as it seems I would have to use Calendar Date/Calendar Range NOT the Member Value parameter/filter. Moreover, if I use 2 parameters for dates for, it does not seem to work (see attached image). Basically on the image, I need the orange bar to shift by a month to the right so orange bars compare correctly to blue bars.

Another issue related to this is that I cannot find a way to address this issue via creating Tokens and moreover, the tokens I do create do not appear in my list on a date filter - not sure if this is related to me creating a Date hierarchy within Dundas and using DateID (integer) as a key for that hierarchy and Dundas may be expecting date time format?

Hopefully, I am not completely confusing here :)…

Thank you!

Trend_example
Trend_example.jpg1000×464 51.4 KB

#2

Hi @semir.taletovic,

Why not consider using Period over Period for something like this? If you add both measures to a metric set and then create a 1-month offset PoP for the second series, you’d always have your trailing 1-month value. The only thing is that this would be displayed on the same point in the axis and not shifted but the value would be correct.

POP
POP.gif1920×1040 909 KB

I would also argue that this is easier to read but you would of course understand your business need better. There’s always exceptions.

1 Like
#3

Thanks Jeff!

Funny thing I literarily tried this within 20 minutes of posting this question - disappointed with myself that I have not thought of PoP capability before as I have used it many time before :). It worked great and a lot better because this way we can use a SINGLE date filter (calendar range) v. 2 Member Value filters which makes navigation a bit harder/more confusing in my opinion. That said, hugely appreciate your quick reply. Also been enjoying your ‘Off the Charts with Jeff’ videos so far :). Keep up a great work.

Thank you.

#4

Glad to hear you have this solved @semir.taletovic and I appreciate the feedback very much.

Cheers!