Please Help. DAX coding

[Steve44]

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],

            STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),

            __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])

            )

        )

)

[v-deddai1-msft]

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

[Steve44]

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.

[v-deddai1-msft · Accepted solution]

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 =
ADDCOLUMNS (
    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

[Greg_Deckler]

@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

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.