Rolling Avg - DATESBETWEEN not working in Table Visual

[Flo1]

Hi at all,

I am using the AdventureWorksDW20 Dataset Model from DA-100 and try to calculate a Rolling AVG of my Sales data as a measure.

In a Line Chart the blue DAX-code below is working as you see in the image.

Rolling AVG =

VAR _StartDay = DATEADD('Date'[Date],-10,DAY)

VAR _EndDay = DATEADD('Date'[Date],10,DAY)

--VAR _StartDay2 = LASTDATE(DATEADD('Date'[Date],-10,DAY))

--VAR _EndDay2 = LASTDATE(DATEADD('Date'[Date],10,DAY))

VAR _Dates = DATESBETWEEN('Date'[Date],_StartDay,_EndDay)

--VAR _Dates = DATESBETWEEN('Date'[Date],_StartDay2,_EndDay2)

VAR _Result =

CALCULATE(

SUM(Sales[Sales])/COUNTROWS(_Dates),

_Dates

)

RETURN

--_StartDay

--COUNTROWS(_StartDay)

_Result

Line Chart -> Rolling AVG is working fine

In a Table Visual the same code is not working. The Error is "A table of multiple values was supplied where a single value was expected." but Filter context should be the same like in the
line chart visual (single dates in first column of the table).

I tried to debug my code -> with LASTDATE( .. ) it is working, BUT: if I return COUNTROWS(_StartDay) my values in the table are at most 1 (see figure), meaning the table visual should work.

If I return _StartDay itself in my measure, I get the same error above.

How is this possible?

[Flo1]

Hi @amitchandak,

thanks for your answer! Your DAX Code is very useful to me for better understanding DAX.

But the question above is still confusing me. In the last figure we see, that the Table Variable _StartDay has at most one row inside (for every filter context in the table visual). But if I return _StartDay itself I get the error, that somewhere "multiple values were supplied where a single value was expected". I don't get it.

[Anonymous]

HI @Flo1,

As the document said, the max function should only return one result, can you please share a sample pbix file to test?

MAX function (DAX) - DAX | Microsoft Docs

In addition, you can also try to use the following measure formula if helps:

Rolling AVG =
VAR currDate =
    MAX ( 'Date'[Date] )
VAR _StartDay = currDate - 10
VAR _EndDay = currDate + 10
VAR _Dates =
    CALENDAR ( _StartDay, _EndDay )
VAR _Result =
    CALCULATE (
        SUM ( Sales[Sales] ) / COUNTROWS ( _Dates ),
        FILTER ( ALLSELECTED ( Sales ), [Date] IN _Dates )
    )
RETURN
    _Result

Regards,

Xiaoxin Sheng

[Flo1]

Hi @Anonymous,

thanks for your reply! How can I share a pbix file?

Regards, Flo1

[Anonymous · Accepted solution]

HI @Flo1,

This issue should be related to dateadd function, it works with table and return a table with single column which is not suitable to be used in datesbetween function.

DATEADD function (DAX) - DAX | Microsoft Docs

DATESBETWEEN function (DAX) - DAX | Microsoft Docs
You can try to use follow measure formula to fix the issue:

Rolling AVG 1 =
VAR currDate =
    MAX ( 'Date'[Date] )
VAR _StartDay = currDate - 10
VAR _EndDay = currDate + 10
VAR _Dates =
    DATESBETWEEN ( 'Date'[Date], _StartDay, _EndDay )
VAR _Result =
    CALCULATE ( SUM ( Sales[Sales] ) / COUNTROWS ( _Dates ), _Dates )
RETURN
    _Result

Regards,

Xiaoxin Sheng

[Anonymous]

HI @Flo1,

You can upload a sample file to the network driver and share the link here.

Notice: please not attach any sensitive data to it.

Regards,

Xiaoxin Sheng

[Flo1]

Hi @Anonymous, 

I attached finally a sample file on a github repository.

If you click on this link

https://github.com/FloDBBI/Power-BI-Reports/raw/main/Sales%20Analysis%20-%20Confusing%20Measure.pbix 

you can download the sample file - hope this is helpful.

Best regards

Flo1

[amitchandak]

@Flo1 , Try like

Rolling AVG =
VAR _StartDay = Max('Date'[Date]),-10
VAR _EndDay = Max('Date'[Date])+10
--VAR _StartDay2 = LASTDATE(DATEADD('Date'[Date],-10,DAY))
--VAR _EndDay2 = LASTDATE(DATEADD('Date'[Date],10,DAY))
VAR _Dates = DATESBETWEEN('Date'[Date],_StartDay,_EndDay)
--VAR _Dates = DATESBETWEEN('Date'[Date],_StartDay2,_EndDay2)
VAR _Result =
CALCULATE(
SUM(Sales[Sales])/COUNTROWS(_Dates),
_Dates
)
RETURN
--_StartDay
--COUNTROWS(_StartDay)
_Result

or try like, example

Rolling 10, -10 = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])+10,-20,Day))

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw