cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIacc721 Frequent Visitor
Frequent Visitor

Running 12 months and year data in same chart with proper year value in the measure name?

Hi!

 

I have an ordinary table with dates (by months) and sames.

I am trying to build a report that would show the following 3 sales values in one table/chart when the date "August 2019" is selected in the slicer:

- sum for year 2017 

- sum for year 2018

- sum of Sept 2019 till August 2019.

 

I understand that I can do three separate measures to get each value, but then the measure would not have the year number in it's name. So i could do "Sales for year before previous" "Sames for previous year" and " Last R12 months", but I need the names to be "2017", "2018" and "R12M"

 

My question is: is there a workaround to get all three values (sum of 2017, 2018 and R12M) in one table with current names (i.e. year number as the measure name)? Or I understand that currently there is no way of doing so (I cannot define dynamic measure names) so I just have to come to terms and use generic measure names?

 

BR!

1 ACCEPTED SOLUTION

Accepted Solutions
AnkitBI Established Member
Established Member

Re: Running 12 months and year data in same chart with proper year value in the measure name?

Not possible to dynamically change the measure name. You can create 3 new measures to display the required values based on selected Date and try to OverLap on your visual.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

View solution in original post

3 REPLIES 3
AnkitBI Established Member
Established Member

Re: Running 12 months and year data in same chart with proper year value in the measure name?

Not possible to dynamically change the measure name. You can create 3 new measures to display the required values based on selected Date and try to OverLap on your visual.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

View solution in original post

Community Support Team
Community Support Team

Re: Running 12 months and year data in same chart with proper year value in the measure name?

Hi @PBIacc721 ,

 

Although the measure name cannot be dynamic, we can provide a workaround here, Create a measure to get the value depends on the filter on year.

 

sum for year =
IF (
    HASONEFILTER ( 'Table'[Date].[Year] ),
    CALCULATE ( SUM ( 'Table'[Value] ) ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] >= TODAY () - 365 )
    )
)

And create another measure to show the name.

 

name measure =
IF (
    HASONEFILTER ( 'Table'[Date].[Year] ),
    "sum for year " & SELECTEDVALUE ( 'Table'[Date].[Year] ),
    "sum for year R12M"
)

Create a card visual to put the name measure, hide title of card visual, adjust the font size and color.

 

Then you can use the value measure to create visual, paint the value measure name as the white to hide it, put the name measure into the same place.

 

8 - Copy.PNG9 - Copy.PNG10 - Copy.PNG

 

But unfortunately for table visual it can not paint signel column name as white, this workaround can only paint all the colmn header white and use multi measures to replace the header for dynamic effect.

 

 

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Running 12 months and year data in same chart with proper year value in the measure name?

Hi @PBIacc721 ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)