topic Re: How can we provide the year end string dynamically in TOTALYTD & DATESYTD? in DAX Commands and Tips

How can we provide the year end string dynamically in TOTALYTD & DATESYTD?

Anonymous — Wed, 24 Jul 2024 07:22:41 GMT

Hi ..,

I am facing the issue of specifying the different yearends in TOTALYTD/DATESYTD functions. I require that according to the Site & Phase selected, the yearend be changed accordingly for the calculations. I have written the following dax which is throwing an error. Can anybody suggest a solution to this?

C_RAG% YTD =

var v_yearend = SELECTEDVALUE(Formulae[Contract_End])

Var Validselection = ISFILTERED('Dim_Sites'[Site & Phase]) && HASONEVALUE('Dim_Sites'[Site & Phase])

RETURN

//TOTALYTD( [Actual C_RAG% Static] , ALL('Calendar'[Date]),ALL('Calendar'),v_yearend)

IF(Validselection,CALCULATE([C_RAG%],ALL('Calendar'), DATESYTD('Calendar'[Date],v_yearend)))
There are around 20 distinct values for Site & Phase and hence 20 different calendars followed. I do not want to hardcode it.

Re: How can we provide the year end string dynamically in TOTALYTD & DATESYTD?

bhanu_gautam — Wed, 24 Jul 2024 08:09:39 GMT

Anonymous , Try using below measure

C_RAG% YTD =
VAR v_yearend = SELECTEDVALUE(Formulae[Contract_End])
VAR Validselection = ISFILTERED('Dim_Sites'[Site & Phase]) && HASONEVALUE('Dim_Sites'[Site & Phase])
RETURN
IF(
Validselection,
CALCULATE(
[C_RAG%],
ALL('Calendar'),
DATESYTD('Calendar'[Date], v_yearend)
),
BLANK() // or you can return some default value or calculation if the selection is not valid
)

Re: How can we provide the year end string dynamically in TOTALYTD & DATESYTD?

Anonymous — Wed, 24 Jul 2024 10:22:46 GMT

Hi Gautam,

Thanks for the reply but this dax is also giving error viz. "Only constant date value is allowed as a year end date argument." That is why I wanted a workaround. Now I will have to write separate calculate function for each site & phase. I wanted to give user the flexibility to change the contract end date and without changing pbix, the change will be implemented.

Re: How can we provide the year end string dynamically in TOTALYTD & DATESYTD?

Gaga_Jin — Fri, 26 Jul 2024 08:59:17 GMT

Anonymous , Hi

Try this one:

1. first of all, create calendar table:

Dax Formula:

Calendar = CALENDAR(MIN(YourData[Date]), MAX(YourData[Date]))

2. secondly, Create a measure to get the dynamic year-end date.

DynamicYearEnd =
VAR SelectedYearEnd = SELECTEDVALUE(Formulae[Contract_End])
RETURN
IF(ISBLANK(SelectedYearEnd), DATE(YEAR(TODAY()), 12, 31), SelectedYearEnd)

3. Use "DATESYTD" with Dynamic Year-End:

C_RAG% YTD =
VAR v_yearend = [DynamicYearEnd]
VAR Validselection = ISFILTERED('Dim_Sites'[Site & Phase]) && HASONEVALUE('Dim_Sites'[Site & Phase])
RETURN
IF(Validselection, CALCULATE([C_RAG%], DATESYTD('Calendar'[Date], v_yearend)))

if this helps you, please approve my solution.