Dynamic Month Order Sort

[LakshtSharma99]

Hi Dev,

I have month column in date table from Jan to Dec. I want to sort x axis dynmically based on the data we have in Facts.

For example.. In sep 2023 we have data till last month so x axis should be from Sep 22 to Aug 23 and in next month x axis should be Oct 22 to Sep 23.

Every month sorting should be update by 1 month

Please help its urgent!

[parry2k]

@LakshtSharma99 this is what you should do, basically, you are looking for rolling 12 months of data based on the recent transaction date. This is what you should do: first add a column in the Date table as below

Month = EOMONTH ( 'DateTable'[Date], 0 )

and change its format to whatever way you want to show it. If you want to show this as a just month name then the format string should be "mmm", if it is month and year then it should be "MMM, yyyy"

Add a measure to get the rolling last 12 months data:

Rolling 12 Months = 
VAR __LatestDate = MAX ( FactTable[Date] ) 
CALCULATE ( 
   SUM ( Table[Sales] ), --or whatever measure you want to use
   KEEPFILTERS ( 
      DATESINPERIOD ( 'DateTable'[Date], __LatestDate, -12, MONTH )
   )
)

To visualize, use the newly added column on the x-axis and above measure on the values and that will do it.

[LakshtSharma99]

That didn't worked. Let me explain the requirement again. 

I have monthly data in 10+ facts and they are connected to Dim_Date using YearMonth column like 202101, 202102.

In Dim_Date we have 5 year of data and we have 1 pre-calulated column PeriodIndicator we have 3 values 'CurrentPeriod', 'PreviousPeriod', 'NA'. For latest 1 year data user will use CurrentPeriod in slicer.

Now in some visual I am displaying sales amount trend comparison for Current Period and Previous Period. Since we have latest data till Aug 2023 and last 12 month will be Current Period (From Sep 2022 to Aug 2023) and previous 12 month will be Previous Period (From Sep 2021 to Aug 2022)

Currently I am using DAX sort key to sort MonthName from Sep to Aug. Sep = 1, Oct = 2 ..... Aug 12

But problem is this dax is not dynamic, next month the data will update and Current Period and Previous Period will change from (Sep 2022 - Aug 2023) to (Oct 2022 - Sep 2023). And month name sorting will again start from Sep month only..but it should start from Oct to Sep right..

I need a dynamic solution so that month name will sort dynamically every month based on current period and previous period years..

Please help! @123abc @amitchandak @Greg_Deckler @parry2k @Idrissshatila @HarishKM 

[123abc]

To create a dynamic solution for sorting the month names based on the current and previous periods in Power BI, you can use DAX to calculate the period start and end dates, and then sort the month names accordingly. Here's a step-by-step guide:

1. Create a new table or calculated table in your Power BI data model that lists all the months within the range of your Dim_Date table. You can use DAX to generate a table with one column containing all the month/year values:

MonthList =
VAR MinYearMonth = MIN(Dim_Date[YearMonth])
VAR MaxYearMonth = MAX(Dim_Date[YearMonth])
RETURN
GENERATE(
CALENDAR(
DATE(YEAR(MinYearMonth), MONTH(MinYearMonth), 1),
DATE(YEAR(MaxYearMonth), MONTH(MaxYearMonth), 1)
),
VAR YearMonth = [Date]
RETURN
YEAR(YearMonth) * 100 + MONTH(YearMonth)
)

1. Create a relationship between the "MonthList" table and your fact table using the "YearMonth" column.

2. Create two measures to calculate the start and end dates of the current and previous periods. These measures should update dynamically based on the slicer selection:

CurrentPeriodStart =
VAR SelectedYearMonth = MAX(MonthList[YearMonth])
VAR SelectedYear = DIVIDE(SelectedYearMonth, 100)
VAR SelectedMonth = MOD(SelectedYearMonth, 100)
VAR StartMonth = IF(SelectedMonth >= 9, SelectedMonth - 11, SelectedMonth + 1)
RETURN
DATE(SelectedYear - IF(SelectedMonth < 9, 1, 0), StartMonth, 1)

CurrentPeriodEnd =
VAR SelectedYearMonth = MAX(MonthList[YearMonth])
VAR SelectedYear = DIVIDE(SelectedYearMonth, 100)
VAR SelectedMonth = MOD(SelectedYearMonth, 100)
VAR EndMonth = IF(SelectedMonth >= 9, SelectedMonth, SelectedMonth + 12)
RETURN
DATE(SelectedYear, EndMonth, 1)

PreviousPeriodStart =
VAR SelectedYearMonth = MAX(MonthList[YearMonth])
VAR SelectedYear = DIVIDE(SelectedYearMonth, 100)
VAR SelectedMonth = MOD(SelectedYearMonth, 100)
VAR StartMonth = IF(SelectedMonth >= 9, SelectedMonth - 23, SelectedMonth - 11)
RETURN
DATE(SelectedYear - IF(SelectedMonth < 9, 2, 1), StartMonth, 1)

PreviousPeriodEnd =
VAR SelectedYearMonth = MAX(MonthList[YearMonth])
VAR SelectedYear = DIVIDE(SelectedYearMonth, 100)
VAR SelectedMonth = MOD(SelectedYearMonth, 100)
VAR EndMonth = IF(SelectedMonth >= 9, SelectedMonth - 12, SelectedMonth)
RETURN
DATE(SelectedYear - IF(SelectedMonth < 9, 1, 0), EndMonth, 1)

Create a calculated column in your "MonthList" table to label each month based on the current and previous periods:

PeriodLabel =
VAR CurrentStartDate = [CurrentPeriodStart]
VAR CurrentEndDate = [CurrentPeriodEnd]
VAR PreviousStartDate = [PreviousPeriodStart]
VAR PreviousEndDate = [PreviousPeriodEnd]
VAR MonthDate = [Date]

RETURN
IF(
AND(MonthDate >= CurrentStartDate, MonthDate <= CurrentEndDate),
"Current Period",
IF(
AND(MonthDate >= PreviousStartDate, MonthDate <= PreviousEndDate),
"Previous Period",
"NA"
)
)

1. Finally, in your visual that displays the sales amount trend, use the "MonthList[MonthDate]" column as the x-axis, and use "MonthList[PeriodLabel]" for color-coding or filtering to distinguish between the current and previous periods. This way, the sorting of month names will be dynamic based on your slicer selection.

Now, as you update the slicer to choose a different period, the month names will sort correctly based on the selected current and previous periods.

[123abc]

To achieve a dynamic month order sort on the x-axis of your visualizations based on the data in your facts table, you can follow these steps in Power BI:

1. Create a new column in your date table that calculates the last available date in your facts table. You can use the following DAX formula:

LastAvailableDate = CALCULATE(MAX(Facts[Date]), ALL(Facts))

This column will give you the last date for which you have data in your facts table.

1. Create another calculated column in your date table that calculates the start date for your x-axis. You can use the following DAX formula:

StartDate = EDATE([LastAvailableDate], -11) // This subtracts 11 months from the last available date to get the start date for the x-axis.

1. In your visualizations, use the "Month" column from your date table for the x-axis.

2. In the "Sort By Column" option for the "Month" column in your visualizations, select the "StartDate" column you created in step 2. This will dynamically sort the months based on the start date calculated for the x-axis.

Now, as your facts data updates each month, the x-axis will automatically adjust to display the last 12 months, sorted dynamically based on the last available date in your facts table.