cancel
Showing results for
Did you mean:
Highlighted
New Member

I need to alter the DAX formula to return P01,P02,P03 etc. instead of the month name. This is using a quick measure for rolling 12 months.

Please assist in where and what formula i can add to the quick measure dax to achieve this.

Thanks

Quick measure code below.

Average of Value rolling average =
IF(
ISFILTERED('Flat File For BI'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Flat File For BI'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Flat File For BI'[Date].[Date],
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Flat File For BI'),
'Flat File For BI'[Date].[Year],
'Flat File For BI'[Date].[QuarterNo],
'Flat File For BI'[Date].[Quarter],
'Flat File For BI'[Date].[MonthNo],
'Flat File For BI'[Date].[Month]
),
__DATE_PERIOD
),
CALCULATE(
AVERAGE('Flat File For BI'[Value]),
ALL('Flat File For BI'[Date].[Day])
)
)
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

Hi @Steve44 ,

It seems that you fiscal year is start on July. If you want to sort the x axis by:P01,P02,P03. Please follow the steps below(Your really create a date dimension table).

1. Create a date table:

``````Dates =
CALENDAR ( "2018, 01, 01", " 2020, 12, 31" ),
"Year", YEAR ( [Date] ),
"Month Year Num", CONCATENATE ( YEAR ( [Date] ), FORMAT ( [Date], "MMM" ) ),
"Month Num", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"Quarter Num", FORMAT ( [Date], "Q" ),
"Quarter", CONCATENATE ( "Q", FORMAT ( [Date], "Q" ) ),
"Day", FORMAT ( [Date], "D" ),
"Week", CONCATENATE ( "Week ", FORMAT ( [Date], "WW" ) )
)``````

Create a  FYMonthNum for the new table:

``````FYMonthNum =
VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
MONTH ( Dates[Date] ) >= FYStartMonth,
MONTH ( Dates[Date] ) - ( FYStartMonth - 1 ),
12
+ (
MONTH ( Dates[Date] ) - ( FYStartMonth - 1 )
)
)``````

3. Creat a new column for the type you need :

``fymonthtype = "P0"&Dates[FYMonthNum]``

4.Sort the colum by FYMonthNum column:

5. Create a relationship between your fact table(Flat File For BI) and the date/calendar table and use the date column in dates table when you create the rolling average and use fymonthtype for x-axis filed.

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

Best Regards,

Dedmon Dai

4 REPLIES 4
Highlighted
Microsoft

Hi @Steve44 ,

Your reuqirements is not clear based on your description. Would you please show us some data and excepted ouput for reference?

Best Regards,

Dedmon Dai

Highlighted
New Member

Hi, thanks for the response.

So i have used a quick measure for rolling 12 month average.

on the x axis I need to display monthly results which is working fine with the quick measure however it only displays the full month name, January, february, march etc.

My reporting needs to be displayed with each month of the financial year as a period so i need it to essentially do this;

July = P01

August = P02

September = P03.

I cannot figure out how to have the date be displayed differently, it keeps just showing the full month name.

Highlighted
Super User IX

@Steve44 - Perhaps create a table like:

January, P01

Febuary, P02

March, P03

Etc, relate this to your data table, then you could use Column2 in your visual.

But, this is all speculation, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Microsoft

Hi @Steve44 ,

It seems that you fiscal year is start on July. If you want to sort the x axis by:P01,P02,P03. Please follow the steps below(Your really create a date dimension table).

1. Create a date table:

``````Dates =
CALENDAR ( "2018, 01, 01", " 2020, 12, 31" ),
"Year", YEAR ( [Date] ),
"Month Year Num", CONCATENATE ( YEAR ( [Date] ), FORMAT ( [Date], "MMM" ) ),
"Month Num", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"Quarter Num", FORMAT ( [Date], "Q" ),
"Quarter", CONCATENATE ( "Q", FORMAT ( [Date], "Q" ) ),
"Day", FORMAT ( [Date], "D" ),
"Week", CONCATENATE ( "Week ", FORMAT ( [Date], "WW" ) )
)``````

Create a  FYMonthNum for the new table:

``````FYMonthNum =
VAR FYStartMonth = 7 //Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
MONTH ( Dates[Date] ) >= FYStartMonth,
MONTH ( Dates[Date] ) - ( FYStartMonth - 1 ),
12
+ (
MONTH ( Dates[Date] ) - ( FYStartMonth - 1 )
)
)``````

3. Creat a new column for the type you need :

``fymonthtype = "P0"&Dates[FYMonthNum]``

4.Sort the colum by FYMonthNum column:

5. Create a relationship between your fact table(Flat File For BI) and the date/calendar table and use the date column in dates table when you create the rolling average and use fymonthtype for x-axis filed.

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

Best Regards,

Dedmon Dai