topic Re: Custom Year to Date Flag in DAX Commands and Tips

Custom Year to Date Flag

Andrew_Butcher — Thu, 23 Nov 2023 12:47:26 GMT

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)

Re: Custom Year to Date Flag

oritam3210 — Thu, 23 Nov 2023 22:13:48 GMT

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!

Re: Custom Year to Date Flag

Andrew_Butcher — Wed, 29 Nov 2023 12:16:24 GMT

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

Re: Custom Year to Date Flag

oritam3210 — Wed, 29 Nov 2023 13:25:10 GMT

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-authoring-and-creating-relationships-in-the-properties-pane/?cdn=disable

I think that a quick solution for your case,

Good Luck!