Calculated Column based on Date Period selected in a Slicer

[apoje]

Hi, 

I am new to the calculated fields in BI. I created a dashboard, where I can see the product sales based on a date selection in a slicer. The date selection is periodical. 

I would like to know how much of a certain unit is sold per day. So if I select a range in a slicer this needs to calculate the sales number that was returned from selection divided by number of days. 

 

How can I achieve this result? 

Thank you! 

Andraz

[v-frfei-msft]

Hi @apoje ,

To create a measure as below.

average per day = 
DIVIDE ( SUM ( 'Table'[sales] ), DISTINCTCOUNT ( 'Table'[date] ) )

[apoje]

Hi @v-frfei-msft, 

I have used the formula that you have suggested, but I do not recieve a desired output.

Average_per_Day = DIVIDE(SUM('py-sales-data-raw'[OrderItem.quantity]), DISTINCTCOUNT('py-sales-data-raw'[OrderDate.date]))

Please check the output:

I set the date for the full April and I recieve an average per day for the first product od 1.04 per day, where the monthly sales were 650. So the result should be 22 per day. 

Do you see where I did a mistake and/or what do I need to adjust?

Thanks for the help!

Andraz 

[v-frfei-msft · Accepted solution]

Hi @apoje ,

Sorry for misunderstanding your request. please updated the measure as below.

average per day =
VAR k =
    CALENDAR (
        CALCULATE (
            MIN ( 'py-sales-data-raw'[OrderDate.date] ),
            ALLSELECTED ( 'py-sales-data-raw' )
        ),
        CALCULATE (
            MAX ( 'py-sales-data-raw'[OrderDate.date] ),
            ALLSELECTED ( 'py-sales-data-raw' )
        )
    )
RETURN
    DIVIDE ( SUM ( 'py-sales-data-raw'[OrderItem.quantity] ), COUNTROWS ( k ) )

[apoje]

Thanks @v-frfei-msft !