cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating fiscal year using DAX function?

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.
1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User IV

@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

Proud to be a Super User!

Frequent Visitor

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

Announcements