Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
Still learning PowerBI but not new to BI.
Data is held in weekly buckets.
I want to do a YOY comparion and have month name (MMM) on my x axis.
The line should follow the data points of the weekly buckets (not by month).
Axis = Month (MMM) (Text type field)
Legend = Year.
1. Grouping by Month (MMM) (fail), and displaying month name (correct!)
Axis = Week Number (number type field)
Legend = Year.
2. Grouping by week (correct!), and displaying week numbers on axis (fail)
Axis = Date (Date Type field)
Legend = Year.
3. 2. Grouping by week (correct!), and displaying month name (correct)! however I've had to select Year = 2018 to force this view.
When I clear the filter, I get this view (certainly a fail!)
Some tips would be appreciated!
@BlueSky , Prefer to create a date table and use that for time intelligence. Have a week, month year etc in the date table. Month does not have year context to yoy will have an issue
sales =SUM(Sales[Sales Amount])
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
YOY = [sales]- [Year behind Sales]
YTD diff
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)
// Year wise
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.
Appreciate the reply (even if it does seem a little genric and doesn't answer the question!).
I already have a fact table and a date dimenion table.
The data types are setup correctly (from what I can see).
"YOY = [sales]- [Year behind Sales]"
This is YOY Variance, that's not what I'm showing. (likewiese for YTD Diff).
With others systems I've used you can set the format of the date field on the chart.
So for example Date (01/01/2020) can be formatted as 'Jan'. It still remains a 'Date' field. That does not seem possible in PowerBI.
The only workaround I can see is to have graph type 1 layered and background set 100% Transparent over graph type 2 (where I have set the data colours to white).
This is not perfect, as you can see the 'March' is not showing correctly.
Any other ideas welcomed.