Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
BlueSky
Helper I
Helper I

YOY linegraph to show short month names but summarise by weekly buckets

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

 

11

Axis = Week Number (number type field)
Legend = Year.
2. Grouping by week (correct!), and displaying week numbers on axis (fail)

22

 

 

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.

3a3a

When I clear the filter, I get this view (certainly a fail!)

3b3b

 

Some tips would be appreciated! 

2 REPLIES 2
amitchandak
Super User
Super User

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

11

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.