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
Lewis_S_William
Frequent Visitor

Previous Year To Date

Hi,

 

Apologies as I know this has been asked and answered numerous times before.

 

I'm trying to get the sales for LY 01/01/2022 to todays date but unsure on how to go about it.

 

I tried to use CALCULATE(SUM(Transactions[Sales]),SAMEPERIODLASTYEAR(Calendar[Date])) but this just pulls up the full year sales of previous years.

 

Any help would be greatly appreciated.

 

Thanks,

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @Lewis_S_William 

you need some code to reflect YTD, try like:

CALCULATE(
    SUM(Transactions[Sales]),
    DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
)

View solution in original post

9 REPLIES 9
Greyesbud
Regular Visitor

H

 

FreemanZ
Super User
Super User

hi @Lewis_S_William 

you need some code to reflect YTD, try like:

CALCULATE(
    SUM(Transactions[Sales]),
    DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]))
)

All of the replies were giving me full years value rather than same period for some reason.

 

I updated my calendar table which was 2020 - 2029 to 2020 - todays date and this one appeared to be better suited for my needs but still with a slight issue, it rolls up january and full month of february for the previous year rather than 01/01 - 20/02

 

Solved it by marking as date table! Thanks 🙂

 

 

Did you test my formula, I specifically made it same period?

 

FILTER(ALL(Calendar),YEAR(Calendar[Date]) = todayYear -1

&& Month(Calendar[Date]) <= todayMonth

&& Day(Calendar[Date]) <= todayDay

Yes, it's pulling up FY, unsure why.

 

Lewis_S_William_0-1676971413654.png

 

Can you show the relationships between the table and the date dimension?  It seems you might have a problem there.

Lewis_S_William_1-1677148156902.png

 

 

BrianConnelly
Resolver III
Resolver III

I am oversimplify so you can follow the DAX and test, but you need to start with wrting some variables.

PreviousYearTodate=

VAR todayYear = Year(Today())

VAR todayMonth= Month(Today())

VAR todayDay = Day(Today())

 

RETURN CALCULATE(SUM(Transactions[Sales]),

FILTER(ALL(Calendar),YEAR(Calendar[Date]) = todayYear -1

&& Month(Calendar[Date]) <= todayMonth

&& Day(Calendar[Date]) <= todayDay )

)

ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

LY_to_today =
var LY_Start = DATE(YEAR(TODAY())-1,1,1) return

CALCULATE(SUM('Table (28)'[value]), DATESBETWEEN('Calendar'[Date],LY_Start,TODAY()))

end result:

ValtteriN_0-1676396277488.png



I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Top Solution Authors