topic Last Year Ticket Total for Current Month in DAX Commands and Tips

Last Year Ticket Total for Current Month

LAYACH — Wed, 10 Feb 2021 21:47:17 GMT

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.

Re: Last Year Ticket Total for Current Month

lbendlin — Thu, 11 Feb 2021 02:59:41 GMT

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

Re: Last Year Ticket Total for Current Month

LAYACH — Thu, 11 Feb 2021 13:26:09 GMT

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.

Re: Last Year Ticket Total for Current Month

lbendlin — Thu, 11 Feb 2021 14:23:18 GMT

"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)

Re: Last Year Ticket Total for Current Month

LAYACH — Thu, 11 Feb 2021 15:11:39 GMT

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.

Re: Last Year Ticket Total for Current Month

lbendlin — Thu, 11 Feb 2021 17:51:37 GMT

I would go with one of the time intelligence measures

Re: Last Year Ticket Total for Current Month

LAYACH — Thu, 11 Feb 2021 18:37:45 GMT

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])

)