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
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
Employee
Employee

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.

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.

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.