Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Victor_1706
New Member

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

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 others find it more quickly.

View solution in original post

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 others find it more quickly.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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

 

And thanks for the advice... 

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 others find it more quickly.

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.

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 others find it more quickly.
Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
gselvag
Helper I
Helper I

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors