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

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

     

    • Victor_1706's avatar
      Victor_1706
      New Member
      7 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        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