Calculating fiscal year using DAX function?

[bourne2000]

Hi

I have two tables one is a transaction and another one is a date table

The transaction table has two columns category, and amount. I have ten categories in the category table. I need to filter only three category. Date table has a date and year column.

I want to calculate the fiscal year (7/1/2017- 6/30/2018) using the DAX function.

Need output as below,

Category	Fiscal Year amount
B	15000
C	2000
D	9000

When I use Filter for category and Date, I am able to get the above results. However, when I use the below DAX function, I am getting a different value as well as unable to filter the categories.

fiscial year= TOTALYTD(SUM(TRANSACTIONS[AMOUNT]), DATE[DATE], "6/30")

I need to use the Dax function for both filters and calculating fiscal year. Please advise.

[v-xiaotang · Accepted solution]

Hi @bourne2000 

Not clear about your data model and table structure, so I create a sample, it may give you pointers. See sample file attached below.

-

Create a measure,

Measure = 
VAR _enddate=DATE(SELECTEDVALUE('Table'[fiscal year]),6,30)
var _firstdate = EDATE(_enddate,-12)
return 
CALCULATE(SUM(TRANSACTIONS[AMOUNT]),FILTER(TRANSACTIONS,TRANSACTIONS[DATE]<=_enddate&&TRANSACTIONS[DATE]>_firstdate))

Result:

Best Regards,

Community Support Team _ Tang

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

[amitchandak]

@bourne2000 , This seems correct.

fiscial year= TOTALYTD(SUM(TRANSACTIONS[AMOUNT]), DATE[DATE], "6/30")

You will have YTD for each category. I think YTD will not sum up across category

[bourne2000]

@amitchandak  Fiscal year values are not matching when I use the Dax function. It's correct when I use date slicer.