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.

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.