cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhooger
Helper I
Helper I

Replace YTD with actual year # in Measure

When I make a measure for YTD, I would like that column to show the actual year based on whatever the date context that was selected. For example:

 

YTD Sales = TOTALYTD(SUM('_Sales'[Sales]),'Date'[Date])

 

Instead of showing YTD in the metric name, if we are showing the report as of today (5/21/2020), it might show as a column header as "YTD 2020 Sales"

 

If we are showing with the report filtered to 6/30/2019, then the measure name would display in the report as: "YTD 2019 Sales"

 

I looked in the painter but no luck.

8 REPLIES 8
mahoneypat
Super User
Super User

You could make a measure that you show in a card above that visual/column like:

 

CardMeasure = "YTD Sales for - " & Selectedvalue(Date[Year])

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

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


Thanks Pat. I really want to show it in a table, side by side with another measure for last year to date and variance. Using in a card doesn't let it break down by customers or products. users just like seeing the year # in the column instead of having to find the As of Date somewhere else on the report.

@dhooger , You need to use date table for that

Try

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks but that answer doesn't really help me get the year listed in the column of a table. Yes, I have a date table, and while date intelligence is useful, users often still dump data to Excel and don't see the context used in the Power BI report in the data dump.

That would be a good feature (to rename a column name with an expression).  You should post it as an idea.   I do think with the right formatting you could put the card right over the column header and it might appear as if it is the actual column header.

Regards,

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


So, I come to the community infrequently enough to not know how to post something as an idea. I'm sure that there is some workaround with objects and layouts, but that seems like its begging to not work on different monitors with different resolutions or something like that. Good ideas and I really do appreciate the response! Thank you.

Hi @dhooger ,

 

It is impossible to dynamic show measure name in visual . In addition to the mahoneypat method, you can also create a table for the measure name in each filter case, and create different measures for all filter cases. For more details, please refer to Giotto's reply in  https://community.powerbi.com/t5/Desktop/Create-a-dynamic-measure-column-name/m-p/961419

 

And you can also create an idea for it in  https://ideas.powerbi.com/forums/265200-power-bi-ideas

 

Best Regards,

Dedmon Dai

@dhooger , if you take data that year and start using is that I doubt you can get YTD, To have YTD easily you need to have date table.

These are different version of YTD

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))


YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

// Year vs last year
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

 

 

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.