Last Year Ticket Total for Current Month

[LAYACH]

I am creating a scorecard that shows the number of tickets created for a month using card visuals.  I need to show the current month's number of tickets, the previous month's number of tickets, and the number of tickets one year ago from the current month.

I created these measures:

Current Month:

Current Prod Ct Month = CALCULATE(MAX(DateTable[YearMonthShort]), FILTER(ALL(DateTable), 'Measures for Product Scorecard'[Total Ct Issue ID]))

Current Prod Ct = CALCULATE('Measures for Product Scorecard'[Total Ct Issue ID], FILTER(DateTable, DateTable[YearMonthShort]=[Current Prod Ct Month]))

Previous Month: 

Prev Prod Ct Month = CALCULATE(MAX(DateTable[Last Month]), FILTER(ALL(DateTable), 'Measures for Product Scorecard'[Total Ct Issue ID]))

Prev Prod Ct = CALCULATE('Measures for Product Scorecard'[Total Ct Issue ID], FILTER(DateTable, DateTable[YearMonthShort]=[Prev Prod Ct Month]))

One Year Ago Current Month:

Prev Prod Ct Year = CALCULATE(MAX(DateTable[Last Year]), FILTER(ALL(DateTable), 'Measures for Product Scorecard'[Total Ct Issue ID]))

Prev Yr Prod Ct = CALCULATE('Measures for Product Scorecard'[Total Ct Issue ID], FILTER(DateTable, DateTable[Last Year]=[Prev Prod Ct Year]))

I added two custom columns to my Date Table: Last Month and Last Year and both return the expected results in the table.

I get back the expected information for all of the measures above except for the very last one:  Prev Yr Prod Ct, which is the February 2020 value of 8.  Below is a visual of the month and value side by side.

I am trying to determine what I need to do for this last one to work as well.   Thank you for your assistance.

 

[lbendlin]

I would go with one of the time intelligence measures

[LAYACH]

Using the Quick Measure and starting from Current Month, I get this since I have to pick an exact date in the Quick Measure.  How now can I modify the resulting DAX to get the MAX month to have the DAX that will be correct when the month changes to March?

Count of Issues for Current Month =

CALCULATE(

COUNTA('NQ Work Intake Component Expand'[Issue Key]),

'DateTable'[YEARMONTHSHORT] IN {DATE(2021,2,1)},

ALL('DateTable'[SortMonth])

)

[lbendlin]

"given the other two DAX formulas that are working correctly above"

I would like to challenge you on that. Those can be replaced by simpler version.  I would recommend you look at the "Quick Measures"  options, and/or consult https://daxpatterns.com  for reference implementation.

 Please provide sample data in usable format (not as a picture)

[LAYACH]

I am fairly new to DAX and the possibilities are a bit overwhelming.  What "Quick Measure" would you reccomend that I start with for what I am attempting to do?  I am all for simple.  Thank you.

[lbendlin]

What prevents you from using SAMEPERIODLASTYEAR() ? What is your expected outcome?

[LAYACH]

I would expect the 531 tickets showing in the table to the left.  Any ideas what I need to change for the given DAX?

I did try the SAMEPERIODLASTYEAR but could not figure out how to use it given the other two DAX formulas that are working correctly above.  If you have a suggestion using SAMEPERIODLASTYEAR with my current DAX that is working that would be great as well.

Thank you for your help.