cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

1

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

2

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.

3a

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

3b

Some tips would be appreciated!

2 REPLIES 2
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])
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

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

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

Helper I

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.

1

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors