cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Unable to calculate previous year FY values using SAMEPERIODLASTYEAR

Dear Expertise,

I am new to Power BI. Our fiscal year starts from October to September. I have to compare sales of current fiscal year with sales of previous financial year. I am writing the formula as PrevRev=CALCULATE(SUM('Sales Table'[Sales]),SAMEPERIODLASTYEAR(DateTime[DateTime].[Date])). But is calculating wrongly, it is showing values as current fiscal year.I am missing someting. Please help me .I have uploaded my pbix file for your reference. Thank you.

https://drive.google.com/file/d/1gSTBdH_QTTJi-2AIuX12ZnHdJauB_SCY/view?usp=sharing

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Funny enough the "Time Intelligence" functions actually require a Dates table.  Your table is too granular (including hours), and the date column is not marked correctly.

 

Dates = CALENDARAUTO(9)

Fiscal Year = CONCATENATE("FY",IF(MONTH(Dates[Date])<=9,YEAR(Dates[Date])-1,YEAR(Dates[Date])))

 

lbendlin_1-1597613259551.png

 

In addition your contract date needs to be mapped to a Date type column, and that column then used in the data model.

 

lbendlin_2-1597613316005.png

 

Then everything works as you intended.

 

 

lbendlin_0-1597613137815.png

 

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Funny enough the "Time Intelligence" functions actually require a Dates table.  Your table is too granular (including hours), and the date column is not marked correctly.

 

Dates = CALENDARAUTO(9)

Fiscal Year = CONCATENATE("FY",IF(MONTH(Dates[Date])<=9,YEAR(Dates[Date])-1,YEAR(Dates[Date])))

 

lbendlin_1-1597613259551.png

 

In addition your contract date needs to be mapped to a Date type column, and that column then used in the data model.

 

lbendlin_2-1597613316005.png

 

Then everything works as you intended.

 

 

lbendlin_0-1597613137815.png

 

View solution in original post

Highlighted
Super User IV
Super User IV

@Raj_Springs , Do not use .date.

 

In case you have datetime, in your table. Create a date column

Date = [Datetime].Date.

Join this with date table and then use time intelligence function. Date/calendar table should be marked as a date table. Option on Right-click.

 

Depending on the need you can use SAMEPERIODLASTYEAR or following

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

 

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Many thanks !

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors