YoY Quick Measure - Date Issue

[Anonymous]

Hello,

I am trying to calculate Sales $ year over year change. I have my data by year however, the app will not allow me to add any data into the date field. I have read multiple articles and there are a ton of different options. I wanted to see if there was something that I need to do with my data set that is preventing this from being done.

Thank you

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try measure like this:

Measure =
VAR year =
    MAX ( 'Table'[year] )
VAR last_year_sale =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[sales], 1 ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[vendor] ), 'Table'[year] = year - 1 )
    )
RETURN
    DIVIDE ( MAX ( 'Table'[sales] ), last_year_sale )

Sample .pbix 

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

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try measure like this:

Measure =
VAR year =
    MAX ( 'Table'[year] )
VAR last_year_sale =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[sales], 1 ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[vendor] ), 'Table'[year] = year - 1 )
    )
RETURN
    DIVIDE ( MAX ( 'Table'[sales] ), last_year_sale )

Sample .pbix 

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

[aj1973]

Hi @V-lianl-msft 

Just to add a little detail : you need to divide Max ('Table'[Sales]) / 100 to get the correct result

Amine

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

[aj1973 · Accepted solution]

Sorry guys the Divide formula must be looking like this

Sorry again

Regards

[amitchandak]

@Anonymous , with a date calendar please find various ways to have YOY

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


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)

//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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[ryan_mayu]

@Anonymous 

It's better to create a measure (writing DAX by yourself). You need a datetime table and create the relationship between your table and datetime table. Then you can use dateadd to get the previous year's value.

It will be better if you share the screenshot or sample file.

[Anonymous]

Hello,

@ryan_mayu thank you for your help. My data is summarizing the Vendor # by Year. I can not have the date by month as it will be too many rows. I have an excel doc that caclculates YOY calculations and I am trying to the same in Power BI.

[ryan_mayu]

@Anonymous 

I create a sample data.

Then you create measures to calculate last year value and YOY%

[aj1973]

Hi,

The YoY measure is a Time Intelligent function, for that you need to create a Calendar table in order to use it proparly.

Regards