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 fi...
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.

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.