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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Andrew_Butcher
Frequent Visitor

Custom Year to Date Flag

I would like to have a slicer which I can switch on and off Year to Date, with a year running 1st September to 31st August. I have a column in my calendar table with the formula below which flags YTD for a standard Jan-Dec calendar year, but struggling to work out how to customise this for different date. I have 10 years of data and need it to apply to previous years as well. e. g. 1st November should currently be flagged in this year and previous years, whereas 1st December should not yet be flagged in current or previous current years. Thanks.  

 

= Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)

3 REPLIES 3
oritam3210
Helper I
Helper I

Hi @Andrew_Butcher,
Why do you want to use the calculated column?

you can calculate it on a measure, with DAX function TOTALYTD

 

see definition

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

 

see example

TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL('DateTime'), "6/30") 

 **In this example, year_end_date can be specified as "6/30"

 

linked to Microsoft DAX function https://learn.microsoft.com/en-us/dax/totalytd-function-dax

 

if you have more then one measure,

you can use Calculation Group - it will activate the YTD function  on all measures

 

Good Luck

---------------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!

Hi @oritam3210, thanks for your reply. I had seen that way if doing things, but ideally I want to create the column so it will dynamically flag each date to be included in the custom YTD or not, changing daily. I could then use that as a slicer so I can switch the YTD on or off on a page of my dashboard to flick between the two views, rather than have to have two separate visuals. Do you know of a way I could achieve this? Thanks

Hi @Andrew_Butcher,
Do you want the column to be able to create a filter?

if so, you can achieve this exact functionality by using calculation groups.
you can create two calculation items, one does nothing (YTD off), and one manipulates all the measures on the report to custom YTD behavior.

BTW - there is a new look for calculation group in Power BI, see this link.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-model-explorer-with-calculation-group-au...

 

I think that a quick solution for your case,

Good Luck!

 

---------------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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