Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks, @v-xinruzhu-msft @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.
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.
Proud to be a Super User!
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")
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?
Proud to be a Super User!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |