Total tickets in previous month

[datanau001]

Dearl all,

I created a new measure to look for the total number of tickets in previous month and formula is below:

Total Cases LM = CALCULATE('AMT Trend Cases Detail'[Total Cases];DATEADD('AMT Trend Cases Detail'[Close Date];-1;MONTH))

It works fine when I let in the table more than one month. E.g: Oct and Sep.

However when using the slicer to show just one month E.g Nov than Oct data won’t be displayed.

See below screenshots:

“Cases Last Month 1”, in the column “Total Cases LM” we see it starts with 8 tickets in day 1/10/2018 but when filtering to show only Oct  (screenshot ““Cases Last Month 1”), than column “Total Cases LM” will be empty.

This slicer and the Close Date field in the table are not from same column.

Would you please help in identifying what is wrong with my calculation?

Thank you.

Marcelo

[MFelix]

Hi @datanau001,

ILooking at what you are presenting and saying that the slicer is based on a different table I assume that both have an active relationhsip other wise your table would not be filtered correct?

If this is the case, column have a relationship and your slicer is a date table not only months, you need to make your measure and the date on the visual table to be the one from the slicer other wise the context of your measure is lost and it will not return the last month as you need.

If you redefine your measure to:

Total Cases LM =
CALCULATE (
    'AMT Trend Cases Detail'[Total Cases];
    DATEADD ( SlicerTable[Date]; -1; MONTH )
)

And add the date column on your visual table everything should work with the slicer.

Regards,

MFelix

[datanau001]

Hello MFelix,

I adjusted the measure as per your instruction and it worked.

Regarding your best practice recommendation, I'll create the calendar table and give it a try.

Thank you very much for your support!

Regards

Marcelo

[datanau001]

Hello MFelix,

Thank you for you reply.

I double checked and both columns used are from same table "AMT Cases Trend Detail" ( new print screen Cases Last Month 3).

The other table "Dates" was just added for a test but it is now removed from the report.

Regads

Marcelo

[MFelix · Accepted solution]

Hi @datanau001,

The issue is because of the context of the measure when you have a visual from a single table and you add a slicer then you measure will be filtered accordingly so you will loose the context of the measure, you need to readjust your measure to:

Total Cases LM =
CALCULATE (
    'AMT Trend Cases Detail'[Total Cases];
    DATEADD ( 'AMT Trend Cases Detail'[Close Date]; -1; MONTH );
ALL('AMT Trend Cases Detail')
)

On a best practice I would create a calendar  table and make the measure based on it and add it to your table visual, this will remove the necessaty of the ALL.

Regards,

MFelix