Forum Discussion
Helper IIhelp..calculate Previous year total based on custom dates
hi,
I have a date table and sales table like below. I need to calculate Previous Yr total based on dates selected.
Ex : date slicer will be based on 'This Yr date' column, and if i select 5/15/2021 to 5/18/2021, I want to calculate Sales based on 'Previous Yr Fiscal Date' based on selected dates. So i need to calculate sales for 5/10/2020 to 5/13/2020 which should return 151000 from sales table. It is simple but not able to figure out. Please help
Date table
| This Yr Date | Previous Yr Fiscal date |
| 5/15/2021 | 5/10/2020 |
| 5/16/2021 | 5/11/2020 |
| 5/17/2021 | 5/12/2020 |
| 5/18/2021 | 5/13/2020 |
sales table:
| date | sales |
| 5/10/2020 | 70000 |
| 5/11/2020 | 23000 |
| 5/12/2020 | 45000 |
| 5/13/2020 | 13000 |
| 5/15/2021 | 35000 |
| 5/16/2021 | 12000 |
| 5/17/2021 | 11500 |
| 5/18/2021 | 42000 |
Hi, srk_powerbi
Thank you for your feedback.
I am not quite sure how your whole data model looks like. But by only seeing this sample, I think one of many ways to achieve this is to create an inactive relation ship with 'Date Table'[previous Yr Fiscal Date] and 'sales table'[date] like the below.
Please check the link down below.
Sales Previous Yr Fiscal Date =CALCULATE (SUM ( 'sales table'[sales] ),USERELATIONSHIP ( 'Date Table'[Previous Yr Fiscal date], 'sales table'[date] ))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
4 Replies
- Greg_Deckler
Community Champion
srk_powerbi You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 - Jihwan_Kim
Super User
Hi, srk_powerbi
Please check the below picture and the sample pbix file's link down below.
Sales Previous Yr Fiscal Date =CALCULATE (SUM ( 'sales table'[sales] ),KEEPFILTERS (FILTER (ALL ( 'sales table' ),'sales table'[date] >= MIN ( 'Date Table'[Previous Yr Fiscal date] )&& 'sales table'[date] <= MAX ( 'Date Table'[Previous Yr Fiscal date] ))))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
srk_powerbiHI Jihwan_Kim thanks fo rreply.
The above solution is working fine. However, i have a question, I see there is no relationship set up between two tables, so dont we need any relationship between tables? Ideally i need to have relationship set up between This yr date field in date table a date field in sales table because i have other measures that i need to create. But when i set up relationship its not working, when start date and end date in date slicer is same.
i have 1 to many relationship. Can you help.
- Jihwan_Kim
Hi, srk_powerbi
Thank you for your feedback.
I am not quite sure how your whole data model looks like. But by only seeing this sample, I think one of many ways to achieve this is to create an inactive relation ship with 'Date Table'[previous Yr Fiscal Date] and 'sales table'[date] like the below.
Please check the link down below.
Sales Previous Yr Fiscal Date =CALCULATE (SUM ( 'sales table'[sales] ),USERELATIONSHIP ( 'Date Table'[Previous Yr Fiscal date], 'sales table'[date] ))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
