Latest year vs previous year MAX?

[adavid999]

Hi I am working with data that includes years (year is the only date needed - not days months, qtrs etc) that update once a year and rather than using actual dates in my measures e.g. "2020" etc, what is the most efficient way of constructing measures that will allow me to ensure I don't have to rewrite all my measures when data is updated based on year?

Eg. something that will alllow me to construct measures like [this year] - [previous year] that will automatically pick up the latest date when dataset is updated. Do I need a calendar table for this and if so, do I use MAX? If yes, how do I combine MAX with CALCULATE?

Many Thanks!

A

[amitchandak]

@adavid999 , In case you have Date in table. Use time intelligence and date table

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

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

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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

Also, refer my time intelligence blog

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...
Appreciate your Kudos. 

[adavid999]

Hi @amitchandak I am still trying to figure this out. I now have a separate date table called My_date_table with dates 2015, 2016, 2017 (I only need years). I link this to my main data on year column which has those years but will soon be updated with 2018. I write my measure like this, using your example:

=CALCULATE (
AVERAGE ( [Sales] ),
FILTER ( ALL ( My_date_table ), [Date_year] = MAX ( My_date_table[Date_year] ) )
)

But the values in this measure change when I put a filter/slicer on my matrix and select, say, 2016. I am looking for a measure that will always show latest year. Can you see where I am going wrong? Possibly I haven't explained this very well...

Many thanks,

A

[v-eachen-msft]

Hi @adavid999 ,

You could add ALL() in your MAX() formula to get latest year.

CALCULATE ( MAX ( My_date_table[Date_year] ), ALL ( My_date_table ) )

Whatever you select on the slicer will return the latest year.

[adavid999]

thanks @v-eachen-msft could you let me know how to combine your recommendation with my calculation or similar for average sales (pasted below)? I have tried but can't figure out.

Ideally I would like two measures - one to show latest date (year) average sales, and another to show last year average sales. The idea is that these would automatically update when a new year's worth of data is loaded to the sales table. And will save me rewriting year-specific measures.

=CALCULATE (
AVERAGE ( [Sales] ),
FILTER ( ALL ( My_date_table ), [Date_year] = MAX ( My_date_table[Date_year] ) )
)

[sturlaws]

Hi @adavid999 

if this is still an issue, you can rewrite your measures to this

latest year =
VAR _maxYear =
    CALCULATE ( MAX ( My_date_table[Date_year] ), ALL ( My_date_table ) )
RETURN
    CALCULATE (
        AVERAGE ( [Sales] ),
        FILTER ( ALL ( My_date_table ), [Date_year] = _maxyear )
    )

and, assuming My_date_table[Date_year] is an integer with just the year value:

last year =
VAR _maxYear =
    CALCULATE ( MAX ( My_date_table[Date_year] ), ALL ( My_date_table ) ) - 1
RETURN
    CALCULATE (
        AVERAGE ( [Sales] ),
        FILTER ( ALL ( My_date_table ), [Date_year] = _maxyear )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[adavid999]

thanks @amitchandak i am still quite new to this. I am working in excel power query/ power pivot. if i wanted to create a date table using calendarauto, where would i write this? in power pivot?

A