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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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.

 

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.