cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper V

## How to sort data based on date

I am trying to sort  data  on my power BI report  based on one column in one table based and  date column from another table in  , i wanted to know if there is a DAX function   for sorting data in this way

1 ACCEPTED SOLUTION
Resolver III

But in that case wouldn't it just sort by Descending date?
I apologize for the questions, but I want to better understand to help you properly.

Please mark as solution so others can find this solution.

13 REPLIES 13
Helper V

my syntax correct though?

Resolver III

What you will have to do is create a column that transforms the date into numeric code and sort by it.

12/01/2020 = 20201201

Please mark as solution so others can find this solution.

Resolver III

Hello, how are you?
I don't know if it's your case.
But if you have a date table and it is correctly related to your fact table.
Just go to Power Pivot, select your date table and sort the name of the month based on the numeric month column.

Please mark as solution so others can find this solution.

Helper V

This is different from what i want to do, i dont have  date table , but i do have table (fact) that has date column and another table (dim) that has the  product ID. so i wanted to sort prodct ID based on the date

Resolver III

Could you share a sample of your data.
Or a fictional base.

Please mark as solution so others can find this solution.

Helper V

Here, D is the date column, and P, is the product ID,  in this screenshot, i would like say the 1/12/20 date and the product ID,s ending with 791 to be at the top

Resolver III

I understood.
My question is what would be the criterion that you will adopt based on the data to consider 139791 and 12/01/20 as the first item on the list.
After knowing this sorting logic, we have a route to follow.

Please mark as solution so others can find this solution.

Helper V

Beacuase thats the newest date , it should be at the top

Resolver III

But in that case wouldn't it just sort by Descending date?
I apologize for the questions, but I want to better understand to help you properly.

Please mark as solution so others can find this solution.

Helper V

I am trying to sort by that and i dont think i finding the place to do that, when i go to the model tab and select that column then try to sort, its not showing the output i need, its just showing all the date without any sort order

Resolver III

Create a custom column in Power Query:

= Table.AddColumn(#"Changed Type", "KeyId", each Text.Combine({Date.ToText([Date], "yyyy"), Date.ToText([Date], "%M"), Date.ToText([Date], "dd")}), type text)

then set this column as number:

Please mark as solution so others can find this solution.

Helper V

I tried this code and my column shows "table"

--here is my code

Table.AddColumn(#"Changed Type", "KeyId", each Text.Combine({Date.ToText([orderdate], "yyyy"), Date.ToText([orderdate], "%M"), Date.ToText([orderdate], "dd")}), type text)

Resolver III

You can share a file with a faithful sample of your data so I can test it.

Please mark as solution so others can find this solution.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors