help..calculate Previous year total based on custom dates

[srk_powerbi]

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

[Jihwan_Kim]

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] )

)

)

)

https://www.dropbox.com/s/7uo70o1gctr3see/srk.pbix?dl=0 

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_powerbi]

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.

[Jihwan_Kim · 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.

Sales Previous Yr Fiscal Date =

CALCULATE (

SUM ( 'sales table'[sales] ),

USERELATIONSHIP ( 'Date Table'[Previous Yr Fiscal date], 'sales table'[date] )

)

https://www.dropbox.com/s/7uo70o1gctr3see/srk.pbix?dl=0 

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

[Greg_Deckler]

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