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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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

you need some code to reflect YTD, try like:

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

Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Anonymous
Not applicable

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors