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

 Date Amount 1-Jan-16 1000 5-Jan-16 500 2-Feb-16 400 11-Feb-16 1700 2-Jan-17 700 10-Jan-17 500 20-Jan-17 1000 3-Feb-16 2000

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 Super User III

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. Proud to be a Super User!

Power BI Blog

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

 Date Amount 1-Jan-16 1000 5-Jan-16 500 2-Feb-16 400 11-Feb-16 1700 2-Jan-17 700 10-Jan-17 500 20-Jan-17 1000 3-Feb-16 2000

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

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.

Proud to be a Super User!

Power BI Blog

Highlighted Helper V

Hi @GilbertQ,

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 Super User III

Hi @RMV

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

Proud to be a Super User!

Power BI Blog

Highlighted Helper V

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 Super User III

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. Proud to be a Super User!

Power BI Blog

Highlighted Helper V

Hi @GilbertQ,

Thanks! The formula works. Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### 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
Users online (1,777)