topic Re: TOTALYTD using variable year end date in DAX Commands and Tips

TOTALYTD using variable year end date

C4L84 — Fri, 18 Mar 2022 17:27:17 GMT

I am using this formula for running total:

No issues with this but I am also using a parameter based on month int:

I need the year end date to change in the formula.

I have tried to use the power query editor to create a concatenated value:

[Query="
declare @StartMonthInt int; set @StartMonthInt = '"&Start_Month_Int&"'

declare @CurrentFY int; set @CurrentFY =
(
select
case when [MonthNum] < @StartMonthInt then datepart(yy,dateadd(year, -1, [DateFull]))

else year([DateFull]) end

from [CommRS_Core_Live].[dbo].[RS_Dates]

where
[DateFull] = cast(getdate() as date)
)

declare @FYStartDate date; set @FYStartDate =
(
select
min([DateFull])

from [CommRS_Core_Live].[dbo].[RS_Dates]

where
datepart(yy,[DateFull]) = @CurrentFY

and datepart(mm,[DateFull]) = @StartMonthInt
)

declare @FYEndDate date; set @FYEndDate = (select dateadd(dd, -1, dateadd(year, 1, @FYStartDate)))

--select '" & "' + cast(datepart(dd,@FYEndDate) as varchar(2)) + '/' + cast(datepart(mm,@FYEndDate) as varchar(2)) + '" & """' [FY end dd/mm]

select @FYEndDate [FY end]
"]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "FY end", "FY end - Copy"),
#"Extracted Day" = Table.TransformColumns(#"Duplicated Column",{{"FY end - Copy", Date.Day, Int64.Type}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Day",{{"FY end - Copy", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"FY end - Copy", "FY end - Day"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "FY end", "FY end - Copy"),
#"Extracted Month" = Table.TransformColumns(#"Duplicated Column1",{{"FY end - Copy", Date.Month, Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Month",{{"FY end - Copy", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"FY end - Copy", "FY end - Month"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Combine({[#"FY end - Day"],[#"FY end - Month"]},"/")),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Custom", "FY end dd/mm"}})
in
#"Renamed Columns2"

I then passed this through to the formula like so:

However it looks as though the formula is defaulting to 31/1 rather than 31/3 (in this instance where parameter value is 4, ie April):

Do you have any idea how I can "trick" the TOTALYTD formula into accepting the concatenated value as a date value, or is there an alternative?

Cheers

Re: TOTALYTD using variable year end date

Tutu_in_YYC — Fri, 18 Mar 2022 18:44:57 GMT

Im curious now, can you try this? Since the end date is of type text i.e "31/01". Use the function FORMAT to convert your date to text.

Running Total Spend =
VAR _EndDate = FORMAT( MAX('FY end'[Date]), "DD/MM")
RETURN

TOTALYTD( SUM('Invoiced Sales'[Period]), 'Calendar'[Date], _EndDate)

You may need to tweak this according to the date table you have

Re: TOTALYTD using variable year end date

C4L84 — Mon, 21 Mar 2022 14:30:11 GMT

Thanks for the suggestion Tutu_in_YYC, I tried formatting as a variable but got this error:

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Unofrtunately, I am struggling to find out what this actually means and how to resolve!

Re: TOTALYTD using variable year end date

Tutu_in_YYC — Mon, 21 Mar 2022 15:00:55 GMT

Thanks for letting me know. I tried the syntax and you are right, it failed. Somehow it doesnt recognize the data type. I am looking into this.

Re: TOTALYTD using variable year end date

v-chenwuz-msft — Tue, 22 Mar 2022 06:36:40 GMT

Hi C4L84 ,

You missed a argument befor "year_end_date", [filter].

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

So if you have a calendar table, please try this

Measure = TOTALYTD(SUM('Table'[Values]),'calendar'[Date],ALL('calendar'),"6/30")
if no calendar table,
Measure 2= TOTALYTD(SUM('Table'[Values]),'Table'[Date],all('Table'),"6/30")
result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mbers find it more quickly.

Re: TOTALYTD using variable year end date

C4L84 — Tue, 22 Mar 2022 10:20:35 GMT

I've included the ALL in the filter arguement but I am now getting this error:

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

I have a pbix that I could share but I cannot find a way to upload...?

Re: TOTALYTD using variable year end date

C4L84 — Tue, 22 Mar 2022 10:49:23 GMT

I've uploaded the file to dropbox, here's the link

Re: TOTALYTD using variable year end date

Tutu_in_YYC — Tue, 22 Mar 2022 17:53:18 GMT

Thanks for the pbicx, this one is tricky. I tried to broke it down to the bare functions, but still have issues trying to make it sustainable.

Re: TOTALYTD using variable year end date

Tutu_in_YYC — Tue, 22 Mar 2022 18:34:33 GMT

Hey C4L84,
Can you try this? I used your pbix, and it looks like it works. I added a calculated column in your Calendar table:

Calendar Year = YEAR('Calendar'[Date])

Then this measure:

Running Total Spend - Dynamic = 

VAR _month = FORMAT(MAX('FY end'[Date]), "M")
VAR _day = FORMAT(MAX('FY end'[Date]), "D")
VAR _endofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day)
VAR _startofyear = DATE(SELECTEDVALUE('Calendar'[Calendar Year]),_month,_day) +1
VAR _startdatedynamic = IF( MAX('Calendar'[Date]) < _startofyear, EDATE(_startofyear,-12), _startofyear)
VAR _enddatedynamic = IF( MAX('Calendar'[Date]) > _endofyear, EDATE(_endofyear,12), _endofyear)
RETURN

CALCULATE(
    SUM(Sheet1[Spend]),
    FILTER(
      ALL('Calendar'),
      'Calendar'[Date] >= _startdatedynamic &&
      'Calendar'[Date] <= _enddatedynamic &&
      'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

Compared above measure with the time intelligence DAX function, the yield same results

Let me know how it goes.

Re: TOTALYTD using variable year end date

C4L84 — Wed, 23 Mar 2022 12:02:14 GMT

Hi Tutu_in_YYC this is excellent! Thank you very much for your assistance - it's much appreciated! Well done