How to make chronological order using drived date column in desktop?

[Sampathkumar_v]

Hi Folks, @Anonymous  @v-jianboli-msft 

I have a proper date column "date" in my table out of which I need to write below logic to get the outcome. How can we sort this output into chronological order. Please advice

DAX Code:

Planned billing = SWITCH(TRUE(),

'Report'[date]=BLANK(),"NB",

YEAR('Report'[date]) =YEAR(TODAY)) && MONTH('Report'[date])<MONTH(TODAY()), "Upto " & format(eomonth(today(),-1),"mmm-yyyy"), FORMAT(Report'[date],"MMM YY"))

Outcome of this above code:

Dec-23

Jan-24

Mar-24

Nov-23

Non Billable

Oct-23

Upto Sep-2023

as if now I am created separate table in desktop putting correct order now this is hard code but I need this to be automated.

[danextian]

Hi @Sampathkumar_v ,

Assuming that NB and Upto Sep-2023 comes before the other, try this as a calculated column to sort your Planned Billing column by:

SORT =
SWITCH (
    TRUE (),
    ISBLANK ( 'Report'[date] ), " 1",
    YEAR ( 'Report'[date] ) = YEAR ( TODAY () )
        && MONTH ( 'Report'[date] ) < MONTH ( TODAY () ), " 2",
    FORMAT ( 'Report'[date], "YYYYMM" )
)

Here's how you can sort a column by another.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto... 

[Sampathkumar_v]

Hi danextian,

actual order coming in my view as alphabetical oder. this need to sort chronological.

Dec-23

Jan-24

Mar-24

Nov-23

Non Billable

Oct-23

Upto Sep-2023

your logic we won't get the steps "Upto " & format(eomonth(today(),-1),"mmm-yyyy") 

[danextian]

Hi @Sampathkumar_v ,

Can you please share a samle pbix with confidential data removed? Also, you still haven't mentioned how Planned billing is to be sorted? What order should they be sorted by? Which one comes first, next, last?

[Sampathkumar_v]

DAX code:

Planned = SWITCH(TRUE(),

'Report'[date]=BLANK(),"NB",

YEAR('Report'[date]) =YEAR(TODAY)) && MONTH('Report'[date])<MONTH(TODAY()), "Upto " & format(eomonth(today(),-1),"mmm-yyyy"), FORMAT(Report'[date],"MMM YY"))

Below the outcome of dax code:

Dec-23

Jan-24

Mar-24

Nov-23

Non Billable

Oct-23

Upto Sep-2023

I need to sory by below order this should be automatic 

Dec-23	4
Jan-24	5
Mar-24	6
Nov-23	3
Non Billable	7
Oct-23	2
Upto Sep-2023	1

If any date less then current month we consider as a "Upto Sep 2023" when we are in next month it will changes to "Upto Oct 2023". in my outcome of the column data type becase a text being a type text power bi will sort the column sqeuence in albhabitically. 

[Anonymous]

Hi @Sampathkumar_v 

You can refer to the following solution

Sample data

1.Create a date column

Date =
VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ),
            "Format", FORMAT ( [Date], "MMM-YY" ),
            "Format2", FORMAT ( [Date], "MMM-YYYY" )
        ),
        [Format],
        [Format2]
    )
VAR b =
    COUNTROWS (
        FILTER (
            a,
            OR (
                CONTAINSSTRING ( EARLIER ( 'Table'[Column1] ), [Format] ),
                CONTAINSSTRING ( EARLIER ( 'Table'[Column1] ), [Format2] )
            )
        )
    )
RETURN
    IF (
        b > 0,
        DATEVALUE (
            MAXX (
                FILTER (
                    a,
                    OR (
                        CONTAINSSTRING ( EARLIER ( 'Table'[Column1] ), [Format] ),
                        CONTAINSSTRING ( EARLIER ( 'Table'[Column1] ), [Format2] )
                    )
                ),
                [Format2]
            )
        )
    )

2.Create the rank column

Rank =
VAR a =
    ADDCOLUMNS (
        'Table',
        "Rank1", RANKX ( FILTER ( 'Table', [Date] <> BLANK () ), [Date],, ASC )
    )
RETURN
    IF (
        [Date] <> BLANK (),
        MAXX ( FILTER ( a, [Column1] = EARLIER ( 'Table'[Column1] ) ), [Rank1] ),
        MAXX ( a, [Rank1] ) + 1
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.