Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Sadly, its not possible to use dynamic variables in YTD functions
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")
Sadly it seems you cant use dynamic variables in ytd functions. Everything is so contrived in dax
@PowerBITestingG
Can you share a copyable dataset in text form?
Just a sample that I Can test my analyses.
User | Count |
---|---|
90 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |