Forum Discussion

Stanil's avatar
Stanil
Regular Visitor
3 years ago
Solved

Slicer value for calculating average

Hello,   as my problem is more complex, I need to present some sample data. Table "sales" contains this sample data: Transaction ID Product Vendor SalesDate Sales Hour 304 ProdA VendA...
Need Help
Tutorial Requests
  • Stanil's avatar
    Stanil
    3 years ago

    It works! however I must reveal your "secret ingredient" - you modified the calculating the date table, from

     

    Date =
    VAR MinYear = YEAR ( MIN ( 'Facts20'[SalesDate] ) )
    VAR MaxYear = YEAR ( MAX ( 'Facts20'[SalesDate] ) )
    RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO( ),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
        )
    )

    to

     

    Date =
    VAR MinYear = MIN ( 'Table'[SalesDate] )
    VAR MaxYear = MAX ( 'Table'[SalesDate] )
    RETURN
    ADDCOLUMNS (
        CALENDAR(MinYear,MaxYear)
    )
     
    And that is the most important change you did. 🙂
     
    Thank you.
Stanil's avatar
Stanil
Regular Visitor
3 years ago

It works! however I must reveal your "secret ingredient" - you modified the calculating the date table, from

 

Date =
VAR MinYear = YEAR ( MIN ( 'Facts20'[SalesDate] ) )
VAR MaxYear = YEAR ( MAX ( 'Facts20'[SalesDate] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    )
)

to

 

Date =
VAR MinYear = MIN ( 'Table'[SalesDate] )
VAR MaxYear = MAX ( 'Table'[SalesDate] )
RETURN
ADDCOLUMNS (
    CALENDAR(MinYear,MaxYear)
)
 
And that is the most important change you did. 🙂
 
Thank you.