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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

nandic I want a Last YTD total which is different from Last Year total. So in order to do that, I tried this query which is not giving any value:

Last YTD = 

var _cy = MAX('Table'[ImpYear]) RETURN CALCULATE([regular_Current Year], MONTH(Table[ReportedDate])<=MONTH(TODAY())-1, 'Table'[ImpYear]=_cy)+0
 
Or may be is there any other efficient way to find the Last YTD?

 

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

Anonymous , attached new version of the file, i missed that you wrote last year "ytd".
New version of the file is focused on regular measures (i deleted disconnected table and measures).

Past YTD =
var _currentYear = MAX('Table'[ImpYear])
RETURN
CALCULATE([Current Year],'Table'[ImpYear]=_currentYear-1, 'Table'[Day of Year]<=DAY(TODAY()))
For this purpose i added new column in table which calculates day number of the year.
Day of Year =
DATEDIFF ( DATE ( YEAR ( 'Table'[ReportedDate] ), 1, 1 ), 'Table'[ReportedDate], DAY ) + 1
 
Also, i added more data in data source so that you can check it.
 
Regards,
Nemanja Andic
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    nandic Since I have data for new months loaded, it seems that the Past YTD does not calculate the right amount. For example, currently it's March and ideally the formula should have added the amounts for the month of January, February and March of previous year, however, it only displays the amount for January. Could you please help me fix this?

     

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

      Hi Anonymous ,
      It was my mistake, i have updated the formula and data in the file so you can check it.

      Formula:

      Past YTD v2 =
      VAR _currentYear =
          MAX ( 'Table'[ImpYear] ) - 1 -- get last year 
      VAR _TodayDate =
          TODAY () -- today date
      VAR _CriteriaDate =
          DATE ( _currentYear, MONTH ( _TodayDate ), DAY ( _TodayDate ) ) -- calculate same day previous year
      RETURN
          CALCULATE (
              [Current Year],
              'Table'[ImpYear] = _currentYear,
              'Table'[ReportedDate] <= _CriteriaDate
          )

       

      If you select 2020, measure "Past YTD v2" will calculate amount for period 1. Jan 2019 - 2. March 2019.

      Regards,
      Nemanja Andic