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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
srk_powerbi
Helper II
Helper II

help..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 DatePrevious Yr Fiscal date
5/15/20215/10/2020
5/16/20215/11/2020
5/17/20215/12/2020
5/18/20215/13/2020

 

sales table:

datesales
5/10/202070000
5/11/202023000
5/12/202045000
5/13/202013000
5/15/202135000
5/16/202112000
5/17/202111500
5/18/202142000

 

 

1 ACCEPTED SOLUTION

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.

 

Picture1.png

 

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


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, @srk_powerbi 

Please check the below picture and the sample pbix file's link down below.

 

Picture3.png

 

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


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

HI @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.

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.

 

Picture1.png

 

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


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Greg_Deckler
Community Champion
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-TIT...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors