cancel
Showing results for
Did you mean:
Member

## SUM in dynamic period

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?

3 REPLIES 3
Community Support Team

## Re: SUM in dynamic period

Hi @rimgri ,

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.

Highlighted
Member

## Re: SUM in dynamic period

Thanks @v-xuding-msft

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

Community Support Team

## Re: SUM in dynamic period

Hi @rimgri ,

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_9O1DE...

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.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 194 members 2,129 guests
Recent signins: