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...
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

gselvag's avatar
gselvag
Helper 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.