Tracking previous months data alongside current month

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!