Tracking previous months data alongside current month

[zfesler]

 

Morning!

Is there a way that I can track the previous month's exceptions alongside the current month? The left widget is working as intended with the max value listed being the total exceptions of the previous month, and the ~46k is the current exceptions for the month. 

I was hoping that on the widget on the right, that I could track the previous month's exceptions so that we can see where we are currently trending in relation to the same time frame from the previous month. 

[david-h]

Hi,

Did you try "pastyear" function + setting the current month filter on the dashboard level ? You can use quick functions from the menu or PASTYEAR function in the formla editor. Or I did not understand what you want to achieve ...

Best, David.

[zfesler]

I probably didn't explain myself fully.

I'm wanting the widget on the right to show me the running total of last month's exceptions in order to visually see if we are trending behind or ahead of where we were at exactly 1 month ago. 

The widget on the right will start from 4/1/22 and increment up from there as we progress through the month of May (so as of today it would be the Sum[Exceptions] from 4/1/22 through 4/9/22). However, when June roles around, I'm wanting it to change and show May's data.

This is how I'm essentially wanting it to show up, but I have to go into the widget and adjust the date range manually as of now. The ~195k for the max on the "Current Month" is the grand total from the previous month of April as well.

I hope that makes more sense.

Thanks for your input!

[KatieG · Accepted solution]

Hi @zfesler - I think something like this could work:

The solution is a combination of an additional column in the model and a built in function we can use in designing the widget:

In the model (live or elasticube)

Add a custom column to your date dimension table that determines whether the date is included in the month to date (MTD) calculation (where [Date] is the date column)

-- DateDiff column
CASE
  WHEN GetDay([Date]) <= GetDay(Now()) THEN 1
  ELSE 0
END

This will act as a flag for whether to include the data or not

In the widget/dashboard:

For this example, I have data for:

• March 2022: Daily profit $1
• April 2022: Daily profit $5
• May 2022 thru May 10th: Daily profit $10

To design the dashboard - I added a date filter of Timeframe "This Month"

"This month to date" widget is just total profit

"Last month to date" uses the PASTMONTH formula

And a widget level filter of DateDiff = 1

This results in a dashboard that looks like this:

Creating a pivot table with the same data and flag shows this method seems to be accurate

Caveats and considerations:

Consider:

• Query Path: You should verify that this change does not cause an M2M relationship (in this reply I focus on adding the flag logic in the dimension but it depends on what your model looks like). Check out the query using the JAQLine plugin to see what the resulting query path is 
• Data frequency: This solution assumes live query or that data models are built on some frequency (probably at least daily).

Hope this helps!

[zfesler]

Thank you @KatieG! This is exactly what I was trying to accomplish.