Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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.
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
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.
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] ) )
)
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.
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |