Dynamic Comparison Between Periods using Parameter

[markzuev96]

Hello, I have created dynamic Comparison Between Periods using Parameter and DAX measures. It works, however, sometimes shows this error

My DAX measure looks like this:

Cost % Change vs Selected Comparison =

VAR SelectedStart = MIN(DateTable[Date])

VAR SelectedEnd = MAX(DateTable[Date])

VAR NumDays = SelectedEnd - SelectedStart + 1

VAR PrevStart_Period = SelectedStart - NumDays

VAR PrevEnd_Period = SelectedStart - 1

VAR PrevStart_Month = EDATE(SelectedStart, -1)

VAR PrevEnd_Month = PrevStart_Month + NumDays - 1

VAR MaxDaysCurrentMonth = DAY(EOMONTH(SelectedStart, 0))

VAR MaxDaysPreviousMonth = DAY(EOMONTH(PrevStart_Month, 0))

VAR ComparisonType = SELECTEDVALUE(ComparisonType[Comparison])

-- Validation for Previous Month: must not exceed month length

VAR IsMonthValid =

    NumDays <= MaxDaysCurrentMonth &&

    NumDays <= MaxDaysPreviousMonth

-- Set previous range based on comparison type

VAR PrevStart =

    IF(ComparisonType = "Previous Month XX Days", PrevStart_Month, PrevStart_Period)

VAR PrevEnd =

    IF(ComparisonType = "Previous Month XX Days", PrevEnd_Month, PrevEnd_Period)

VAR IsValid =

    IF(ComparisonType = "Previous Month XX Days", IsMonthValid, TRUE)

VAR Curr =

    CALCULATE(

        sum(Final_Media[COST]),

        FILTER(

            ALL(DateTable),

            DateTable[Date] >= SelectedStart &&

            DateTable[Date] <= SelectedEnd

        )

    )

VAR Prev =

    CALCULATE(

       SUM(Final_Media[COST]),

        FILTER(

            ALL(DateTable),

            DateTable[Date] >= PrevStart &&

            DateTable[Date] <= PrevEnd

        )

    )

RETURN

    IF(

        IsValid && NOT(ISBLANK(Prev)),

        DIVIDE(Curr - Prev, Prev),

        BLANK()

    )

[burakkaragoz · Accepted solution]

Hi @markzuev96 ,

Looking at your DAX, the "can't determine a single value" error usually happens when your parameter table or date selections are returning multiple values instead of single ones.

Most likely culprits:

Parameter selection issue: Your SELECTEDVALUE(ComparisonType[Comparison]) is probably returning BLANK() sometimes, which means either no comparison type is selected or multiple ones are selected. Add some debugging:

VAR ComparisonType = 
    IF(
        ISBLANK(SELECTEDVALUE(ComparisonType[Comparison])), 
        "Previous Period", -- default value
        SELECTEDVALUE(ComparisonType[Comparison])
    )

Date filter context problems: When there's no clear date selection, MIN(DateTable[Date]) and MAX(DateTable[Date]) might be acting weird. Try this:

VAR SelectedStart = 
    IF(
        ISFILTERED(DateTable[Date]),
        MIN(DateTable[Date]),
        BLANK()
    )
VAR SelectedEnd = 
    IF(
        ISFILTERED(DateTable[Date]),
        MAX(DateTable[Date]),
        BLANK()
    )

Add an early exit:

-- Right after your date variables
RETURN
IF(
    ISBLANK(SelectedStart) || ISBLANK(SelectedEnd) || ISBLANK(ComparisonType),
    BLANK(),
    -- rest of your calculation here
)

Quick test: Put your measure in a card visual with no filters applied. If it shows the error, then it's definitely a parameter/context issue.

The error usually shows up when users haven't made proper selections yet, or when the report first loads. Adding those validation checks should fix it.

Try the parameter default value fix first - that's usually the main culprit.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[markzuev96]

thank you

[v-lgarikapat]

Hi @markzuev96 ,

Thanks for reaching out to the Microsoft fabric community forum.

Could you please provide sample data that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format (e.g., Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.

Looking forward to your response.

Best regards,
Lakshmi