cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dunner2020
Post Prodigy
Post Prodigy

Changing the name of measures dynamically (according to year or month)

I got a fact table and date as a dimension table. I calculated three measures related to registration using Fact table. One measure is total registration in last FY, the second one is about total registration in YTD (means current financial year), and last one is about total registration in Last month. I named my measure as follow:

 

New Registration - Last FY = ...........

New Registration - YTD = ...............

New Registration- Last Month = ...........

 

I used these measure to plot the bar chart as shown in the following figure:

 

leo_89_0-1634255558747.png

 

I want to show the measure name in the graph in such a way that if it is 'New Registration - Last FY' then it should be shown as 'FY21' or if it is 'New Registration - YTD' then it should show as 'FY22'. My desired output looks like as follow:

 

leo_89_1-1634255752424.png

 

Is there any way of doing that dynamically in Power BI?

 

 

1 ACCEPTED SOLUTION

Hi @Dunner2020 

The X Axis column is dynamicly.  I add a sort column in calculated table, then we can use sort in measure to determind dynamic FY, FY YTD and Month.

 

X Axis =
VAR _T =
    {
        "FY" & ""
            & FORMAT ( TODAY (), "yy" ) & " " & "YTD",
        "FY" & ""
            & FORMAT ( TODAY (), "yy" ) - 1,
        "Mon" & ""
            & FORMAT ( TODAY (), "mm" ) - 1
    }
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Sort",
            IF (
                CONTAINSSTRING ( [Value], "YTD" ),
                2,
                IF ( CONTAINSSTRING ( [Value], "Mon" ), 3, 1 )
            )
    )
RETURN
    _T2

 

FY will always show FY+Last year number, FY YTD will alway show FY + current year +YTD, and mon will alway show mon + last month. 

Ex:

Year = 2021 month = 10, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon9(last month ),

Year = 2021 month = 11, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon10(last month ),

Measure:

 

Measure = SWITCH(SUM('X Axis'[Sort]),1,[New Registration - Last FY],2,[New Registration - YTD],3,[New Registration- Last Month])

 

Result is as below. 

1.png

Best Regards,
Rico Zhou

 

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

 

View solution in original post

4 REPLIES 4
RicoZhou
Community Support
Community Support

Hi @Dunner2020 

If you only add three measures into the value field to create the column chart, you couldn't see measure name in X aixs.

If you want to show names in X axis, you need to add columns in X axis. I suggest you to create a caluclated table with all names you need. Then create a new measure to combine three measures to one by names.

 

X Axis = {"FY"&""&FORMAT(TODAY(),"yy"),"FY"&""&FORMAT(TODAY(),"yy")-1,"Mon"&""&FORMAT(TODAY(),"mm")-1}

 

Then new measure is as below.

 

Measure = SWITCH(MAX('X Axis'[X axis]),"FY20",[New Registration - Last FY],"FY21",[New Registration - YTD],"Mon9",[New Registration- Last Month])

 

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

@RicoZhou , Thanks for your reply. 

 

The only issue with the solution is that I have to change the name of the month every month in the measure. Otherwise, it would not work.

Hi @Dunner2020 

The X Axis column is dynamicly.  I add a sort column in calculated table, then we can use sort in measure to determind dynamic FY, FY YTD and Month.

 

X Axis =
VAR _T =
    {
        "FY" & ""
            & FORMAT ( TODAY (), "yy" ) & " " & "YTD",
        "FY" & ""
            & FORMAT ( TODAY (), "yy" ) - 1,
        "Mon" & ""
            & FORMAT ( TODAY (), "mm" ) - 1
    }
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Sort",
            IF (
                CONTAINSSTRING ( [Value], "YTD" ),
                2,
                IF ( CONTAINSSTRING ( [Value], "Mon" ), 3, 1 )
            )
    )
RETURN
    _T2

 

FY will always show FY+Last year number, FY YTD will alway show FY + current year +YTD, and mon will alway show mon + last month. 

Ex:

Year = 2021 month = 10, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon9(last month ),

Year = 2021 month = 11, it will show FY 2020(last year), FY 2021 YTD(current year) and Mon10(last month ),

Measure:

 

Measure = SWITCH(SUM('X Axis'[Sort]),1,[New Registration - Last FY],2,[New Registration - YTD],3,[New Registration- Last Month])

 

Result is as below. 

1.png

Best Regards,
Rico Zhou

 

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

 

View solution in original post

mahoneypat
Super User
Super User

You can do that with a Calculation Group.  Create a Calculation Item associated with each measure and name the item how you want it displayed, and then use the "Name" column of the calculation group as the x axis in your visual.

Are you still not using Calculation Groups?! - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors