Forum Discussion
gbarr12345
Post Prodigy
Post ProdigyPower BI - Scalar error
Hello, I am trying to write a DAX query to get the sales contribution by region for the last 90 days and am getting the following error - The expression refers to multiple columns. Multiple colum...
lbendlin Thanks for your contribution on this thread.
Hi gbarr12345 ,
According to your sample data, the order data are on year 2015. Then the condition ( Orders[Order Date] >= TODAY() - 90 && Orders[Order Date] <= TODAY() ) will not return any data, that's why the measure return the blank value...
Best Regards
gbarr12345Post Prodigy
Post ProdigyAh yes I understand. Apologies I should have spotted that.
Thank you for your response.
Is there a good alternative code instead of TODAY() - 90 to get 90 days before that date in 2015?
Many Thanks.
Hi gbarr12345 ,
If you want to get the same day in 2015 with today, please update the formula of measure as below to get it:
Sales Contribution by region =
VAR _date =
TODAY ()
VAR _date1 =
CALCULATE ( MIN ( 'Orders'[Order Date] ), ALLSELECTED ( 'Orders' ) )
VAR _year =
YEAR ( _date1 )
VAR _month =
MONTH ( _date )
VAR _day =
DAY ( _date )
VAR _edate =
EOMONTH ( DATE ( _year, _month, 1 ), 0 )
VAR _ndate =
IF ( DAY ( _edate ) < _day, _edate, DATE ( _year, _month, _day ) )
VAR StartDate = _ndate - 90
VAR EndDate = _ndate
RETURN
MAXX (
TOPN (
1,
SUMMARIZE (
FILTER (
ALLSELECTED ( Orders ),
Orders[Order Date] >= StartDate
&& Orders[Order Date] <= EndDate
),
Orders[Region],
"TotalSales", SUM ( Orders[Sales] )
),
[Total Sales], DESC
),
[Region]
)Best Regards