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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Greg_Deckler
Super User
Super User

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.