Average of AverageX and reshaping calendar for smaller dataset

[Alex_57]

Hello,

I'm trying to calculate the amount of days that products are in stock. The products have a date coming in and only a selling date when the product has been sold. The goal is an stockoverview per Month/Year. In the screenshot below some "sample" data to help outline my problem. 

For starters i made a list of dates (in this case january 1st of 2020 till the 31rd of december) and unfolded that list as dates from 1-1-2020 to 31-12-2020 for each product. I linked those dates with the date calendar. Imagine that the real data contains thousands of products over multiple years which is my first problem. It generates too much rows with dates.

When calculating the days that a product is in stock, the average on the total is incorrect. Let's say i want to see for August what the average of stockdays is:

Although the days in stock are correct for the individual products, it's not correct for the total for august (dateslicer). The correct average should be 120 days, like this:

So it comes down to the combination of 'How to create dates between "date coming in" and "Selling date (or dynamic end date)" and calculating the correct total average per period (month/year). Thank you for your time.

Regards, Alex

[v-jingzhang · Accepted solution]

Hi @Alex_57 

You can try this measure

StockDays =
VAR vMaxDate = MAX ( 'Date'[Date] )
RETURN
    AVERAGEX (
        FILTER (
            'Example Sheet',
            'Example Sheet'[Date coming in] <= vMaxDate
                && (
                    ISBLANK ( 'Example Sheet'[Selling date] )
                        || 'Example Sheet'[Selling date] > vMaxDate
                )
        ),
        DATEDIFF ( 'Example Sheet'[Date coming in], vMaxDate, DAY )
    )

And you don't need to add a list of dates for each product in the table. See the attachment. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Alex_57]

Hello!

Thank you very much. This does the trick and reduces the amount of data (no list of dates required).

Kind Regards, Alex

[amitchandak]

@Alex_57 , if want the max of selected date , then you var should be

var MaxDate = Maxx(allselected(Query), query[Date])

datediff should be surrounded by a calculate

also try to use date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.