The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I am trying to calculate a table where I can see the YTD values with this DAX:
Is YTD = IF ( 'Append'[Attribute - Date] >= DATE ( YEAR ( DISTINCT ( 'Append'[Date Ref.EoMonth] ) ), 1, 1 ) && 'Append'[Attribute - Date] <= DISTINCT ( 'Append'[Date Ref.EoMonth] ), "YTD", "Other" )
But I get error: A table of multiple values was supplied where a single value was expected
Solved! Go to Solution.
You can correct the DAX formula:
Is YTD =
IF (
'Append'[Attribute - Date] >= DATE ( YEAR ( MAX ( 'Append'[Date Ref.EoMonth] ) ), 1, 1 ) &&
'Append'[Attribute - Date] <= MAX ( 'Append'[Date Ref.EoMonth] ),
"YTD",
"Other"
)
Dates from 2024-01-01 to 2024-09-30 will be marked as YTD.
Other dates will be marked as Other.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hi @yolandacb You are getting this error because of DINTINCT function. The DISTINCT function returns a table of unique values, but the IF function expects a single value for comparison. So use aggregator function which returns a single value for example, MAX.
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
You can correct the DAX formula:
Is YTD =
IF (
'Append'[Attribute - Date] >= DATE ( YEAR ( MAX ( 'Append'[Date Ref.EoMonth] ) ), 1, 1 ) &&
'Append'[Attribute - Date] <= MAX ( 'Append'[Date Ref.EoMonth] ),
"YTD",
"Other"
)
Dates from 2024-01-01 to 2024-09-30 will be marked as YTD.
Other dates will be marked as Other.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
What about if I need the sum to start 2024-04-01?
Actually this worked!
Thank you.