Dynamic Variable in Measure

[PowerBITestingG]

Hi all,

I have the following dax measure = 

Spend YTD classic = 
VAR octoberstart1 = SWITCH(TRUE(),
[MaxOctoberStart]=10/03/2022,"10-03",
[MaxOctoberStart]=10/04/2021,"10-04","10-03")
return
TOTALYTD(Sales[US],Time[Date Dt],octoberstart1)

MaxOctoberStart=
CALCULATE (
    MAX ( Time[Fiscal Start Date] ),
    FILTER (
        ALL ( Time ),
        MD_Time[Fiscal Mnth No] = 10
            && MD_Time[Fiscal Yr Int] = MAX ( Sales[Fiscal Year] )
    )
)

But I get True/False expression does not specify a column error

I am simply trying to make the octoberstart1 variable change based on the fiscal start date for each october

Any ideas?

I would greatly appreciate it

[v-henryk-mstf]

Hi @PowerBITestingG ,

As @vojtechsima  said, are you able to provide test data, as no further testing can be done based on the formula you provided alone.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Looking forward to your reply.

Best Regards,
Henry

[PowerBITestingG]

Sadly, its not possible to use dynamic variables in YTD functions

[vojtechsima]

Hi, @PowerBITestingG 
I think here is the problem:

VAR octoberstart1 = SWITCH(TRUE(),
[MaxOctoberStart]=10/03/2022,"10-03",
[MaxOctoberStart]=10/04/2021,"10-04","10-03")

The 10/03/2022 is probably not recognizable because it's not either string or number, so you have to wrap it with "" to make it a string and then make sure, that MaxOctoberStart returns a date in same format.
Or you can check against DATE function and fill it like this:

VAR octoberstart1 = SWITCH(TRUE(),
[MaxOctoberStart]=DATE(2022,3,10),"10-03",
[MaxOctoberStart]=DATE(2021,4,10),"10-04",
"10-03")

[PowerBITestingG]

Sadly it seems you cant use dynamic variables in ytd functions. Everything is so contrived in dax

[vojtechsima]

@PowerBITestingG 
Can you share a copyable dataset in text form?
Just a sample that I Can test my analyses.