Last year Month on Month calculation

[amit_darak]

Hi,

I want to create a custom column chart Month on Month with 2 values.1 for selected fiscal year other for last fiscal year.

As you can see from the below image, when I select FY 22-23, column chart should show value for 22-23 and 21-22.

In the card it show correct value ie 1281. But same measure does not work correctly- when used on column chart Month on Month. Expected value on chart should be like the one in table - Apr-284, May-175 and so on but instead it shows Apr - 1281, May-997

Below is the dax that I have written.

last_year_count =

var min_date_cal_yr = CALCULATE(MIN('Calendar'[Date]), 'Calendar'[Fiscal_Year_Number]>2000)

var min_date_prev_year = DATE(YEAR(min_date_cal_yr)-1,MONTH(min_date_cal_yr),1)

var max_date_prev_year = DATE(YEAR(min_date_cal_yr), 3,31)

var claim_count_rev = CALCULATE([tot_cnt],

                                  ALL( 'Calendar'[Fiscal Year]),

                                         'Calendar'[Date] >= min_date_prev_year &&

                                         'Calendar'[Date] <= max_date_prev_year)

return

claim_count_rev

I tried using month name in All, Allexcept, Allselected but nothing worked.

Regards,

Amit Darak

[ovde · Accepted solution]

Hi @amit_darak,

I suggest using the SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn 

This makes it easier and can possibly achieve your goal without messing with ALL() since you do not need to ignore filters. 

[amit_darak]

Hi,

This did work. Just thinking what was wrong in my DAX?

Regards,

Amit Darak