YonY % change not including futures months

[Anonymous]

i am looking to do a year on year % change by month for this year vs last year. the issue i am having is for the future months for this year its showing -100% which i dont want to take into account i am currently calculating base don the below but i dont want to look at months for 2020 i dont have data for

Date Reference = DATE(2016,12,31)

Enter in the following equation to calculate the last year monthly sales amount.

LastYear = 
  VAR 
    CurrentDate = [Date Reference]
  RETURN
    CALCULATE( 
     SUM(Data[Sales]), 
     Data[Year] = YEAR(CurrentDate)-1
    )

Note: Using the NOW() function calls the current time when the query was last run.  Thus, if you refresh your data next month the NOW() function wrapped in a YEAR() will return the current year from the date-time observed by PowerBI.

Following the same process enter the following additional measures.  The ThisYear measure calculates the sales for the current month.

ThisYear = 
  VAR 
   CurrentDate = [Date Reference] 
  RETURN
   CALCULATE(SUM(Data[Sales]),Data[Year] = YEAR(CurrentDate))

Finally, add the calculation for the Year to Year comparison.

YoY Percent Change = DIVIDE([ThisYear], [LastYear], 0)-1

[amitchandak]

@Anonymous , refer if something like this can help

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)


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)

similar one here

Power BI — YTD Questions — Time Intelligence 1–5
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak thank you,

would i need both these calculations or are you saying i need just 1 of these;

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)

[amitchandak]

@Anonymous , depends; which one you want to control- YTD or last year YTD

[Anonymous]

@amitchandak would i be able to put this into a table that looks at month then sales for this year and last year then YonY as seems this still wont let me do that?

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Create a calendar table and create relationships based on date columns.

Create three measures as this year and last year and difference.

Total Sales = SUM( Sales[Total Revenue] )

Total Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( Dates[Date] ) )

Diff_ = IF( ISBLANK( [Total Sales LY] ), BLANK(), DIVIDE([Total Sales],[Total Sales LY] ))

The date column of the calendar table is used as the row in the matrix, and three measures are used as values.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.