How to order by weeks (chronologically)

[Victor_1706]

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

[v-frfei-msft · Accepted solution]

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 )

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

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

[Victor_1706]

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

And thanks for the advice... 

[v-frfei-msft · Accepted solution]

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 )

For more details, please check the pbix as attached.

Regards,

Frank

[Victor_1706]

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.

[v-frfei-msft · Accepted solution]

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

[Greg_Deckler]

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

[gselvag]

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. 

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.