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.
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi, Stanil ;

You could modify to this:

myavg2 = 
var firstday = CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'))
var lastday =CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))
return
 count('Table'[Transaction ID]) / (datediff (firstday, lastday, day) + 1)

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.