Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jazzk
Helper I
Helper I

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. 

 

ImpYearReportedDateIdAmount
201701/01/2017110
201701/01/201727
201701/01/201739
201801/01/2018123
201801/01/2018215
201801/01/2018319
201901/01/2019112
201901/01/2019217
201901/01/2019318

 

The final output should have 'Year' present in the filter so that I can select any year from my data. Below is my expected output if I have filtered out Year to 2019:

IdCurrent Year TotalPast Year Total
11223
21715
31819

 

Also please note that the 'ReportedDate' column is actually a hierarchy column with day, month, quarter and year where I have consirdered only the 'year' part.

Below is a snippet of my DAX query which does not give me the numbers. 
Past Year Total= CALCULATE(sum(Table[Amount]),FILTER(Table,Table[ImpYear]=Table[ReportedDate].[Year]-1))+0
 
1 ACCEPTED SOLUTION
nandic
Memorable Member
Memorable Member

Hi @jazzk ,
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

 

View solution in original post

10 REPLIES 10
nandic
Memorable Member
Memorable Member

Hi @jazzk , attached power bi file with both scenarios.
One scenario (on the right) which is simpler, it doesn't use disconnected table so everything is based on original (regular) table.

 

regular_Current Year = SUM('Table'[Amount])
 
regular_Past Year =
var _currentYear = MAX('Table'[ImpYear])
RETURN
CALCULATE([regular_Current Year],'Table'[ImpYear]=_currentYear-1)
 
regular_Year over Year = [regular_Current Year]-[regular_Past Year]

 


Second scenario (on the left) if you are using disconnected table.

disconnected_Current Year =
var _currentYear = SELECTEDVALUE('Year'[Year])
RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_currentYear))
disconnected_Past Year =
var _previousYear = SELECTEDVALUE('Year'[Year])-1
RETURN CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ImpYear]=_previousYear))
 
disconnected_Year over Year = [disconnected_Current Year]-[disconnected_Past Year]
 
 

Regards,
Nemanja Andic

@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
Memorable Member
Memorable Member

@jazzk , 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
 

@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
Memorable Member
Memorable Member

Hi @jazzk ,
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 ( _currentYearMONTH ( _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

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1611191656784.png

 

Hello @wdx223_Daniel The above measure that you have created is Past Year Total. How would I calculate Current Year Total?

 

sum(table[amount]

nandic
Memorable Member
Memorable Member

Hi @jazzk ,
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

 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors