cancel
Showing results 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

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

1 ACCEPTED SOLUTION
Super User

Hi, @srk_powerbi

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.

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.

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.

4 REPLIES 4
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.

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.

Helper II

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.

Super User

Hi, @srk_powerbi

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.

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.

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.

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors