Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter amounts by year

Hello Community,   I have a sample dataset given below, where, I want to filter by year and calculate the total amount for each id for current year and past year.    ImpYear ReportedDate Id ...
  • nandic's avatar
    nandic
    5 years ago

    Hi Anonymous ,
    There is option to create disconnected table.
    Example: create new table which will have values 2017, 2018, 2019. This table must not have any connection to existing table where you have measures.

    In slicer use Year from this new disconnected table.
    Now create these 2 measures:

    Current Year =
    var _currentYear = SELECTEDVALUE('Year'[Year])
    RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_currentYear))
    Past Year =
    var _previousYear = SELECTEDVALUE('Year'[Year])-1
    RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_previousYear))
     

    Regards,
    Nemanja Andic

     

nandic's avatar
nandic
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Hi Anonymous ,
There is option to create disconnected table.
Example: create new table which will have values 2017, 2018, 2019. This table must not have any connection to existing table where you have measures.

In slicer use Year from this new disconnected table.
Now create these 2 measures:

Current Year =
var _currentYear = SELECTEDVALUE('Year'[Year])
RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_currentYear))
Past Year =
var _previousYear = SELECTEDVALUE('Year'[Year])-1
RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_previousYear))
 

Regards,
Nemanja Andic

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello nandic ,

 

Thank you for your response! I also wanted to calculate the 'Change YTD', which also be based on the year that will be chosen from the filter.  My current DAX query looks like:

[Current Year]-[Past YTD], where,
[Current Year] will contain the formula that you have mentioned in the previous post, whereas, [Past YTD] =
CALCULATE(sum(Table[Amount]),MONTH(Table[ReportedDate])<=MONTH(TODAY())-1,FILTER(Table,Table[ImpYear]=Year(today())-1))+0
Could you please let me know how can this query be rectified?