Power BI: create a trend chart

It is not as easy as with excel or with ServiceNow because when I take a look in the “visualizations”, I see a lot of options but not the trend one and if I select “stacked column chart”, it will not do the trend. For instance, I have this data:

power bi

Trying to do the trend, I got this:

power bi

It seems correct, right ? But not, because, the “created” date, all are 2021 except February which is 2022 so the order is not correct.

To make it right, I need to create a new column from “data”, in the “table tools”, select “new column”:

power bi

Then put this formula:

FORMAT([created],"YYYY-MM")

NOTE: “created” is the name of my column where there is the date so change it to your column name

It should look like that:

power bi power bi

Then press “enter” in your keyboard or just click on the “OK” button. I will change the name, to do so, just right click on the column and select “rename” or in the formula put:

month = FORMAT([created],"YYYY-MM")

Instead of:

column = FORMAT([created],"YYYY-MM")

power bi power bi

If you want it weekly instead of monthly, the formula will be:

FORMAT([created],"YYYY-WW")

This formula will show the first week with 1 number (for instance 2022-1) instead of 2 (for instance 2022-01), to display it that way:

FORMAT([created],"YYYY")&"-"&FORMAT(WEEKNUM([created],2),"00")

In the other hand, if you are going to use this column to create a weekly trend chart, use this formula:

FORMAT([created],"YYYY")&"-"&FORMAT(WEEKNUM([created],2)-1,"00")

NOTE: 2 is to say that the week begins on Monday, if you want to start on Sunday, put 1.

Now I am going back to my chart, in the “visualizations”, in the “axis”, just add the “month” (the new column) and it will display correctly.

power bi power bi
power bi

Interesting Management