cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Victor_1706 Frequent Visitor
Frequent Visitor

How to order by weeks (chronologically)

 

Hello everyone.

 

 

I have little time using Power BI and I am still adapting to the tool. I'm modeling data to show the total number of organizations for each of the stages in my sales process.

 

My questions are:

 

- How can I sort data by weeks (chronologically) and give them a unique number?

- How can I get a count of days by organizations for each stage or section of the project.

 

I have not been able to make Power BI able to sort the data of my tables per week, since I need to show them that way, the data that I have in my tables are in months and days but I have not been able to order them as I want ... In addition to being able to add the attributes I want.

 

I attach the link to my .pbix file

https://drive.google.com/open?id=14TxYg3AoCf1LFj_DbGF2l7_0Wt66d1G4

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: How to order by weeks (chronologically)

Hi @Victor_1706,

 

I created one measure to get the count of days per organization.

 

 

Measure = 
VAR mindate =
    CALCULATE (
        MIN ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa] )
    )
VAR maxdate =
    CALCULATE (
        MAX ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa] )
    )
RETURN
    DATEDIFF ( mindate, maxdate, DAY )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to order by weeks (chronologically)

Hi @Victor_1706,

 

Which column is the code? You can update the formula to it.

 

Measure = 
VAR mindate =
    CALCULATE (
        MIN ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa],deals[code] )
    )
VAR maxdate =
    CALCULATE (
        MAX ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa],deals[code] )
    )
RETURN
    DATEDIFF ( mindate, maxdate, DAY )

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
gselvag Regular Visitor
Regular Visitor

Re: How to order by weeks (chronologically)

Try by create a column in your Calendar:

WeekNo = WEEKNUM('Calendar'[Date];2) 

You can create a date column (example Year-WeekNo) and sort by created column or any other columns in the Calendar table. 

 

graph_pbi7.JPG

 

Hope this help for the week sort. I can't translate from Spanish in data tables, but assume you need to calculate days between stages of deal. The best way to do this is by create Measure.

Super User
Super User

Re: How to order by weeks (chronologically)

If you need to span years, you can use my Sequential quick measure:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: How to order by weeks (chronologically)

Hi @Victor_1706,

 

Firstly, we need to create a custom column in power query to get the weeknum column by 

 

Date.DayOfWeek

Then we can make other columns sorted by the week number.

 

For the second question, could you please tell me what is your excepted result?

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Victor_1706 Frequent Visitor
Frequent Visitor

Re: How to order by weeks (chronologically)

The excepted result is, the number of days per organization by "etapa" (where the organization is)...

 

And thanks for the advice... 

Community Support Team
Community Support Team

Re: How to order by weeks (chronologically)

Hi @Victor_1706,

 

I created one measure to get the count of days per organization.

 

 

Measure = 
VAR mindate =
    CALCULATE (
        MIN ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa] )
    )
VAR maxdate =
    CALCULATE (
        MAX ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa] )
    )
RETURN
    DATEDIFF ( mindate, maxdate, DAY )

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Victor_1706 Frequent Visitor
Frequent Visitor

Re: How to order by weeks (chronologically)

Hi, @v-frfei-msft, sorry for the absence...

 

The measure doesn't count per organizations, it only counts by "Etapa" but, it's nice... So thanks for help.

Only one cuestion more:

 

- May I change or modify the code to count the days per Organizatios whit the same code?

 

Best regards,

Victor.

Community Support Team
Community Support Team

Re: How to order by weeks (chronologically)

Hi @Victor_1706,

 

Which column is the code? You can update the formula to it.

 

Measure = 
VAR mindate =
    CALCULATE (
        MIN ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa],deals[code] )
    )
VAR maxdate =
    CALCULATE (
        MAX ( deals[Último cambio de etapa] ),
        ALLEXCEPT ( deals, deals[Etapa],deals[code] )
    )
RETURN
    DATEDIFF ( mindate, maxdate, DAY )

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.