cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Please Help. DAX coding

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])
            )
        )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Please Help. DAX coding

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:

Capture.PNG

 

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

 

 

 

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Please Help. DAX coding

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

Re: Please Help. DAX coding

 

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
Super User IX

Re: Please Help. DAX coding

@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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Please Help. DAX coding

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:

Capture.PNG

 

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

 

 

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors