Forum Discussion

Victor_1706's avatar
Victor_1706
New Member
7 years ago
Solved

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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's avatar
    v-frfei-msft
    7 years ago

    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

7 Replies

  • gselvag's avatar
    gselvag
    Icon for Helper I rankHelper I
    7 years ago

    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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Victor_1706
      New Member
      7 years ago

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

       

      And thanks for the advice... 

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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