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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Griff
Helper I
Helper I

New User, struggling to create YTD and previous year YTD values from monthly data set

Hi there

I'm new to Power BI, am currently trying to get to grips with DAX formulas so I can build a table with YTD and YTD PY from source data
Source data (called 'Values' ) is monthly data running from Jan 2019 to April 2020

I've had a bit of help from someone who knows more than me to get started (they are away at the moment though!)
Currently report looks like this

Annotation.png

The data table I was instructed to copy/paste in is called 'Date' and looks like this:

Date =
VAR MinYear = YEAR ( MIN ( MonthlyData[Period] ) )
VAR MaxYear = YEAR ( MAX ( MonthlyData[Period] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Year Month", YEAR ([Date]) & " " & FORMAT ( [Date], "mmm" ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"Weekday", FORMAT ( [Date], "dddd" )
)
I don't know what 'day' and 'weekday' are doing in there when it's just mponthly data, but since I don't understand how date table works I don't want to touch it 🙄

The result?:

'Values' is just what I see when I build a simple table and filter the visual to display 'CY 2020'

'TY YTD' is a measure built with this formula:  

TY YTD = TOTALYTD(SUM(MonthlyData[Values]),'Date'[Date],ALL(MonthlyData))
(numbers look ok BUT I think this is more by luck than design)
 'PY YTD' is a measure built with this formula:
PY YTD = calculate([TY YTD],SAMEPERIODLASTYEAR('Date'[Date]),ALL(MonthlyData))
(numbers are wrong - should be a bit smaller than TY YTD but are instead full year)

Somewhere, something is wrong, but not sure if an issue with the date table or the measure formulas

If I put a page filter and force it to only choose Jan - Apr it all works, but I need a formula that can recognise how many months of the current year are available and work it out automatically without manually filtering each time

Anyone out there got any ideas?

6 REPLIES 6
amitchandak
Super User
Super User

@Griff , If you have date you should able to get using time intelligence and date table

example

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

 

of you have the only month in this format Jan 2019

 

Create a new column like this and change its format to date

Date = "01 " & [Month]

 

Hi @amitchandak

Many thanks for the reply
I inputted your measures (see below) but the results are the same
I think that having it as 12/31 means that although my 2020 data only goes to April, when it does the previous year it is adding up all 12 months that are there
Annotation.png

@Griff , 12/31 or any date indicate where year end . It means YTD should start with Jan. And go till the current month.  Now that month Should Ideally be selected in some slicer. If not Calendar End date will be taken as an end date.

 

I have these few Version of YTD, Which I explained in my Webinar, Link Given below. That you should consider

// Need a date for coorect YTD
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

///Forced without date
YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

///Forced without date, if max date is in current year
YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

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

 

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.

Adding a slicer with month name seems to have done the trick! 
Fantastic - thanks for the help

& the next challenge is how to sort the Month Name in the slicer so it's not in alphabetical order 😄

@Griff , try like

YTD Sales = CALCULATE(SUM(MonthlyData[Values]),DATESYTD('Date'[Date],"12/31"))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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