Calculate Annual Result in Table which contain Date column

[KH_Mike]

Hi All,

I have a data table and would like to show the "Annual Result" like below. Right now, I can use DAX to calculate the YTD result by Fiscal Year "calculate(sum(Table[Result]),DATESYTD(Table[Date],"03/31"))". I want to know is it possible to show the "Annual Result". I try serval DAX express and not able to show like below. Anyone can give me hints .... Thank you.

Date	Result	YTD (Fiscal Year)	Annual Result
2020-04	1	1	78
2020-05	2	3	78
2020-06	3	6	78
2020-07	4	10	78
2020-08	5	15	78
2020-09	6	21	78
2020-10	7	28	78
2020-11	8	36	78
2020-12	9	45	78
2021-01	10	55	78
2021-02	11	66	78
2021-03	12	78	78
2021-04	3	3	102
2021-05	4	7	102
2021-06	5	12	102
2021-07	6	18	102
2021-08	7	25	102
2021-09	8	33	102
2021-10	9	42	102
2021-11	10	52	102
2021-12	11	63	102
2022-01	12	75	102
2022-02	13	88	102
2022-03	14	102	102
2022-04	9	9	55
2022-05	10	19	55
2022-06	11	30	55
2022-07	12	42	55
2022-08	13	55	55

[v-yueyunzh-msft]

Hi, @KH_Mike 

Here are the steps you can follow:

(1)This is my test data, it is the same as you:

(2)You can create a measure:

Measure = var _current_date= SELECTEDVALUE('Sheet1'[Date])
var _month = MONTH(_current_date)
 return 
IF(_month>= 4,CALCULATE(SUM('Sheet1'[Result]), FILTER(ALL('Sheet1') , 'Sheet1'[Date]>= DATE( YEAR(_current_date),4,1) && 'Sheet1'[Date] <=DATE( YEAR(_current_date)+1,3,31))) ,  CALCULATE(SUM('Sheet1'[Result]), FILTER(ALL('Sheet1') , 'Sheet1'[Date]>= DATE( YEAR(_current_date)-1,4,1) && 'Sheet1'[Date] <=DATE( YEAR(_current_date),3,31)))   )

(3)Then we can put this measure in the visual and we can meet your need, the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[KH_Mike]

Hi @v-yueyunzh-msft 

Thank you for your support. It do come out the expected result. However, my dataset also contain certain column like "Product", "Region", "Country" ...... etc.

It seems that your DAX express will not be caculate according to my selection on slicer. Any ideas how to handle it? Thank you.

[v-yueyunzh-msft · Accepted solution]

Hi, @KH_Mike 

If you want to keep the slice filtter you select, you just need to replace the ALL() function to ALLSELECTED() function(), like this:

Measure = var _current_date= SELECTEDVALUE('Sheet1'[Date])
var _month = MONTH(_current_date)
 return 
IF(_month>= 4,CALCULATE(SUM('Sheet1'[Result]), FILTER(ALLSELECTED('Sheet1') , 'Sheet1'[Date]>= DATE( YEAR(_current_date),4,1) && 'Sheet1'[Date] <=DATE( YEAR(_current_date)+1,3,31))) ,  CALCULATE(SUM('Sheet1'[Result]), FILTER(ALLSELECTED('Sheet1') , 'Sheet1'[Date]>= DATE( YEAR(_current_date)-1,4,1) && 'Sheet1'[Date] <=DATE( YEAR(_current_date),3,31)))   )

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[KH_Mike]

Hi @v-yueyunzh-msft 

Thank you. You save my life.