## Max Quantity Between Dates

Anything related to PowerPivot and DAX Formuale
yuin
Posts: 6
Joined: Tue Dec 09, 2014 4:17 pm

### Max Quantity Between Dates

Hi all, referring to attached file which indicates the data and expected output, would appreciate if anyone can advise the right DAX formula to solve the following:

Data:
Table_Raw consists of Details(equipment), Start Date, End Date and Quantity
Table_Date is a normal date table that consists of date, year, month, day

Objective:
To have a sliceable pivot table that have "month" as column and "detail(equipment)" as rows, and table consist of maximum quantity per month (please refer to attached file)

Have tried the following DAX formula but they do not provide the desired outcome.
=sumx( filter(Table_Raw, Table_Raw[Start]<=related(Table_Date[Date])&&Table_Raw[End] >=related(Table_Date[Date] ) ), Table_Raw[Qty.])
=sumx(Table_Raw,(related(Table_Date[Date])>=Table_Raw[Start])*(related(Table_Date[Date])<=Table_Raw[End])*Table_Raw[Qty.])

Attachments
191128 Equipment Qty Matrix Test.xlsx

Bernard Heymans
Posts: 34
Joined: Wed Feb 28, 2018 12:18 am

### Re: Max Quantity Between Dates

Hi @yuin ,

you have to create a line per month for each entry

if I use 3 machines from june until septembre, I need to see thoses 3 machines in june, july, aug, sept....

I did that in the query.

then you just sum the number of machines

Attachments
191128 Equipment Qty Matrix Test.xlsx

yuin
Posts: 6
Joined: Tue Dec 09, 2014 4:17 pm

### Re: Max Quantity Between Dates

Hi Bernard,
First of all thanks for taking the time to reply.
(1) Can you please elaborate a little on how you create a line per month through Power Query (i.e. = Table.AddColumn(#"Removed Columns", "month", each { Date.Year([Start])*12 + Date.Month([Start]) .. Date.Year([End])*12 + Date.Month([End]) }) (did you write this formula or do you use a built-in Transformation step in Power Query?)
(2) Notice that the results are all off by a year. (i.e. Dozer starts in April 2019 but the result shows that it starts in April 2020). Can that be fixed?
Thanks again.

Bernard Heymans
Posts: 34
Joined: Wed Feb 28, 2018 12:18 am

### Re: Max Quantity Between Dates

Hi Yuin,

Actualy if you add a column that contain {15..20} it contains a list between 15 and 20.

If I had added a column with { [Start] .. [End] } , I wouild have a list of days between start and end date...

As I wanted to work on month base, I converted the [Start] date into a number of month (year * 12 + month)
like sept 2019 => 2019 * 12 + 9 = 24228 +9 = 24237 ( or 24237 months between year ZERO and now)

did the same with [end] date...

created the list & opened the list into lines.

That is it

The result is wrong by one year...
change the line that add the column datum
= Table.AddColumn(#"Expanded month", "Datum", each #date( Number.RoundDown(([month] -1 )/12)+1,
Number.Mod([month]-1 ,12)+1,
1))

into
= Table.AddColumn(#"Expanded month", "Datum", each #date( Number.RoundDown(([month] -1 )/12),
Number.Mod([month]-1 ,12)+1,
1))

This is the convert back from months number into calendar dates...

yuin
Posts: 6
Joined: Tue Dec 09, 2014 4:17 pm

### Re: Max Quantity Between Dates

Hi Bernard,
Thanks for the clarification & explanation. Appreciate it. 