Forum Discussion

gbarr12345's avatar
gbarr12345
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Power 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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

gbarr12345's avatar
gbarr12345
Icon for Post Prodigy rankPost Prodigy
2 years ago

Ah 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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