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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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.

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

my syntax correct though?

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

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

rodrigosan
Resolver III
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.

rodrigosan_0-1657636996594.png

 

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Anonymous
Not applicable

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 

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

 

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Anonymous
Not applicable

jokimwele76_0-1657637961100.png

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 

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.

 

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Anonymous
Not applicable

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

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.

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Anonymous
Not applicable

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

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:

rodrigosan_0-1657650337907.png

 

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Anonymous
Not applicable

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)

 

jokimwele76_0-1657651338953.png

 

You can share a file with a faithful sample of your data so I can test it.
upload the file here: https://easyupload.io/
and send me the link.


Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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