Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
@Shubhangi , 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.
@Shubhangi , 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
)
Proud to be a Super User! |
|
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |