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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |