cancel
Showing results for
Did you mean:
Highlighted
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.

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.

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

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 39 members 831 guests
Recent signins: