cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

previous year calculation with time filter

Hi,

 

I have this difficulty in applying a correct formula.

I'm trying to apply DAX formula to get the previous year calculation, while I also have Month Slicer applied for my financial report.

What I have is transaction table, the example is follow

 

DateAmount
1-Jan-161000
5-Jan-16500
2-Feb-16400
11-Feb-161700
2-Jan-17700
10-Jan-17500
20-Jan-171000
3-Feb-162000

 

In the page where I add the visualization, I have a Month slicer. and the formula I tried to apply is:

Amount YTD Previous Year = CALCULATE(SUM(Table[Amount]),FILTER(Table,AND(Table[Date]>=DATE(YEAR(TODAY())-1,1,1),Table[Date]<=DATE(YEAR(TODAY())-1,MONTH(MAX(Table[Date])),DAY(ENDOFMONTH(Table[Date]))))))

But this formula returns blank amount. I'm guessing this is because of the Month slicer applied is for 2017.

For the information, I don't have any other filter in the visual nor page nor report.

 

I tried to use SAMEPERIODLASTYEAR or PREVIOUSYEAR formula, but I guess these work only for unique calendar date (?)

 

I need help, what goes wrong with my formula, and what solution I need to apply.

Thanks.

1 ACCEPTED SOLUTION

Hi @RMV

 

When you created your date table did you create the relationships?

 

I created the following measures based on your dataset.

 

My Amount = SUM(Table1[Amount])

YTD = TOTALYTD([My Amount],'Date'[Calendar Date])

Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))

Which then gave me the following correct output.

 

 Email Pic.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

7 REPLIES 7
Super User II
Super User II

Hi @RMV


What I would suggest doing is to create a date table, after which you could then use the PREVIOUSYEAR and SAMEPERIODLASTYEAR.

You can find details below on how to create the date table, and this is required and recommended for when doing any TIME related DAX functions

 

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...

 

You are indeed correct that if you put in a slicer and click on it, it will then filter your data based on the slicer selected for all your measures on your report. But depending on how you create your measure you might be able to change the filter context for previous periods.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ,

 

thanks for your advise.

I need further advise on using the date table.

I apologize that my example did not really represent the condition.

Beside the Date & Amount column, there're some category columns that will be used to drill down the data from category to category.

 

The table result I'm looking for is as follow

Category 1                   Amount YTD    Amount YTD Previous Year

A

    A-1                            xxx                      xxx

    A-2                            xxx                      xxx

B

    B-1                            xxx                      xxx

Note: A & B are category 1

A-1, A-2, B-1 are category 2

 

If I use the date table, how can I still drill down to categories?

 

regards,

Hi @RMV

 

Yes you will still be able to use the drill down categories





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ,

 

I tried it, and still got blank as the result.

Need further advise on what goes wrong.

 

I created a date table, add a measure column = CALCULATE(SUM(Table[Amount]),PREVIOUSYEAR(DateCalendar[Date]))

 

In the table matrix visualization:

Category                            Amount YTD                   Amount Previous Year

(column from Table)           (measure from Table)    (measure from DateCalendar)

 

Tried CALCULATE(SUM(Table[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))

It results blank too.

Hi @RMV

 

When you created your date table did you create the relationships?

 

I created the following measures based on your dataset.

 

My Amount = SUM(Table1[Amount])

YTD = TOTALYTD([My Amount],'Date'[Calendar Date])

Previous Yr My Amount = CALCULATE([My Amount],PREVIOUSYEAR('Date'[Calendar Date]))

Which then gave me the following correct output.

 

 Email Pic.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Hi @GilbertQ,

Thanks! The formula works.

Helper V
Helper V

Hi,

 

I have this difficulty in applying a correct formula.

I'm trying to apply DAX formula to get the previous year calculation, while I also have Month Slicer applied for my financial report.

What I have is transaction table, the example is follow

 

DateAmount
1-Jan-161000
5-Jan-16500
2-Feb-16400
11-Feb-161700
2-Jan-17700
10-Jan-17500
20-Jan-171000
3-Feb-162000

 

In the page where I add the visualization, I have a Month slicer. and the formula I tried to apply is:

Amount YTD Previous Year = CALCULATE(SUM(GLEntry_all[Amount_]),FILTER(DateCalendar,AND(DateCalendar[Date]>=DATE(YEAR(TODAY())-1,1,1),DateCalendar[Date]<=DATE(YEAR(TODAY())-1,MONTH(MAX(DateCalendar[Date])),DAY(ENDOFMONTH(DateCalendar[Date]))))))

But this formula returns blank amount. I'm guessing this is because of the Month slicer applied is for 2017.

For the information, I don't have any other filter in the visual nor page nor report.

 

I tried to use SAMEPERIODLASTYEAR or PREVIOUSYEAR formula, but I guess these work only for unique calendar date (?)

 

I need help, what goes wrong with my formula, and what solution I need to apply.

Thanks.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors