I track unemployment rates on national, state and county level. See attached workbook.National and state data are typically 1 month behind, counties are 2 months behind.
In my dashboard I show tiles with the latest unemployment rates for each - national, state and county and in each tile I also include previous month rate for each respective geographic area to put the current rates in context. I created several calculations to display my rates correctly.
However, as I display my rate for previous month, I also have a "previous month/year date field" in each tile (see attached image), but I can't seem to figure out how to change it to show the correct month for each tile. Let me explain:
I currently show February unemployment rates in large font for National and Alabama, so these two tiles should have "Jan 2018" in small print underneath the big number to show the rate for previous month.
I currently show January unemployment rates for Baldwin and Mobile counties tiles and these should have Dec 2017 as previous month rate.
I created this "previous month name" calculation {MAX(DATETRUNC('month',[Date]))}-1 but even if I change -1 to -2 or -3, nothing happens to dynamically change my month.
Any suggestions would be much appreciated.
Good afternoon
I'm not certain I have an answer but I can show what is going on
see the attached
see the sheet "MY sheet"
I change the calc for previous month to this - it takes the month and looks back 1 month then determines the first day of the month
your calculation looks like this looks at the first of the month of the max date then looks back 1 day - i.e. the last day of the previous month
But that's OK because each will return February for the month
and Each is correct because the Max Date is in March - and either set of formulas will return February and January
OK So what - the formulas are driven off the March date which is the last (max ) date in the data set
If you want to drive the National data off the February National data that is received in March then you need to create a label - something like
and for the previous months
like this
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
