SUM in dynamic period

[Anonymous]

Hello,

I need to calculate the sum of Sales for the particular date period.
I have two tables: Calendar and Historical Inventory (sales, by day and Item)

Sum up Period:

• Min date - selected calendar date
• Max date - selected calendar date + TOC days (every Item have different TOC day period)

For example: if I have selected 2019-06-12, and TOC day for the item is  8, then Min date - 2019-06-12 and Max day - 2019-06-20

I wrote a DAX, but it brings the sum just for Min date:

Measure 2 = 
VAR mindate =MIN(Calendar[Date])
VAR maxdate = MIN(Calendar[Date])+AVERAGE('Historical Inventory'[TOC days])
RETURN
CALCULATE(
    SUM('Historical Inventory'[Sales]);
    FILTER('Historical Inventory';
    'Historical Inventory'[Date]>=mindate
        && 'Historical Inventory'[Date]<=maxdate))

Maybe someone has any suggestions what's wrong with dax?

[v-xuding-msft]

Hi @Anonymous ,

By my test, you need to add a ALL function in the measure. the following is my sample you can have a try.

I have two tables. The table “Calendar” is a calendar table. And  there is a many-to-one between two tables.

Calendar = CALENDARAUTO()

Measure = VAR mindate =MIN(Calendar[Date])

VAR maxdate = MIN(Calendar[Date])+AVERAGE('Historical Inventory'[TOC days])

RETURN

CALCULATE(

    SUM('Historical Inventory'[Sales]),

    FILTER(ALL('Historical Inventory'),

    'Historical Inventory'[Date]>=mindate

        && 'Historical Inventory'[Date]<=maxdate))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-xuding-msft 

It works with dates, but how I should correct the DAX if I want to see information by Item?

[v-xuding-msft]

Hi @Anonymous ,

Do you mean filter by item using the slicer? If so, it still calculate the sum in dynamic period  when I add the slicer of items.

You can download my sample to reference. If this is not your expected output, please share more details.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-xuding_microsoft_com/EcK49qp8MqhAhlwb3_9O1DEBmUJeQ6EaCqOkRDWRFd1TTQ?e=E8sYy4

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.