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

Postby yuin » Mon Dec 09, 2019 2:21 pm

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.])

Thanks in advance.
Attachments
191128 Equipment Qty Matrix Test.xlsx
(687.01 KiB) Downloaded 7 times

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

Re: Max Quantity Between Dates

Postby Bernard Heymans » Mon Dec 09, 2019 7:39 pm

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


BTW: I deleted your calendar & added mine.
Attachments
191128 Equipment Qty Matrix Test.xlsx
(565.13 KiB) Downloaded 7 times

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

Re: Max Quantity Between Dates

Postby yuin » Tue Dec 10, 2019 10:29 am

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: 33
Joined: Wed Feb 28, 2018 12:18 am

Re: Max Quantity Between Dates

Postby Bernard Heymans » Thu Dec 12, 2019 2:12 am

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

Postby yuin » Fri Dec 13, 2019 8:15 am

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest

cron