Forum Discussion

bourne2000's avatar
bourne2000
Helper V
5 years ago
Solved

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,

 

CategoryFiscal Year amount
B15000
C2000
D9000

 

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.
Need Help
Show and Tell
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      bourne2000
      Helper V
      5 years ago

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    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.