Forum Discussion

PowerBITestingG's avatar
PowerBITestingG
Resolver I
4 years ago

Dynamic Variable in Measure

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

Need Help

5 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 years ago

    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's avatar
      PowerBITestingG
      Resolver I
      4 years ago

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