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.
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.
Solved! Go to 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
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
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.
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
@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.
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.