Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
The data table I was instructed to copy/paste in is called 'Date' and looks like this:
'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:
@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
@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"))
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |