cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted

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
Highlighted
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.

Highlighted
Super User III
Super User III

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

Highlighted

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,

Highlighted

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

Highlighted

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.

Highlighted

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

Highlighted

Hi @GilbertQ,

Thanks! The formula works.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors