Hi Pulkit, you can do this by putting [State] into the rows shelf, discrete months of [Data_Changed] into the columns shelf, and then [Number of Records] into the Text label (or just drop it in where you see a bunch of "Abc" marks).
To add discrete months of [Data_Changed] to the columns shelf, right-click-and-drag it there and then pick MY(Data_Changed).
Ah, I see now. Here you go:
The solution is kind of complicated. Here are the basic steps:
- I created 3 measures (one for each State) that incremented or decremented based on what change had occurred. This only works if you're looking at it per-client, so I added Client ID to rows so it'd be in the LOD.
- I did a running total of all 3 of these measures.
- I did a window sum along all clients (which is similar to Table Down) to get the number of clients in each state for each month.
- I hid the extra rows using a table calculation filter trick (keeping true for FIRST() = 0) and hid the Client ID header.
If you end up doing this yourself, in step #3, note that your table calculation actually has 2 levels, so you'll need to make sure that the running total computes along the date while the window sum computes along Client ID.
State.twbx 10.2 KB