Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-rzhou-msft
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.

@v-rzhou-msft , 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.

 

mahoneypat
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.