How to calculate Minimum Qty between 2 dates from a running sum measure

[Anonymous]

I have table as below with 2 fields Date and QTy.

Date	QTY
20th Oct 2017	300
21st Oct 2017	500
22nd Oct 2017	-200
23rd Oct 2017	100
24th Oct 2017	540
25th Oct 2017	-600
26th Oct 2017	300
27th Oct 2017	250

And as I needed a running total of the field QTY from above table I created the following Measure.

Cumilative Qty:= CALCULATE(SUM(QTY),

                                                FILTER(ALL(DimDate[Date]) , DimDate[Date] <= MAX(DimDate[Date]))

And now the table looks like below.

Date	QTY	Cumilative Sum
20th Oct 2017	300	300
21st Oct 2017	500	800
22nd Oct 2017	-200	600
23rd Oct 2017	100	700
24th Oct 2017	540	1240
25th Oct 2017	-600	640
26th Oct 2017	300	940
27th Oct 2017	250	1190

Now my requirement is to get Minimum Value of Cumilative Qty from above table for last 7 days from the date I am checking .

I tried the following DAX query but it is not working as MIN function only accepts column reference and not measure.

Min Culilative Qty in Last 7 days:= Calculate(MIN(CUMILATIVE QTY),

                                                                      DATESBETWEEN('DIMDate'[Date],FIRSTDATE(DATEADD('DimDate'[Date],-1,YEAR))

                                                                    ,LASTDATE('DimDate'[Date]))

Please do the needful.

[TomMartens]

Hey,

given your sample data in your 1st post, what is your expected result.

Can you please explain, where in the your formula for

Min Culilative Qty in Last 7 days

Are you referring to the last seven days.

But nevertheless, you might try this

Min Culilative Qty in Last 7 days:= 

Calculate(

MINX(
DATESBETWEEN('DIMDate'[Date]
,FIRSTDATE(DATEADD('DimDate'[Date],-1,YEAR))
,LASTDATE('DimDate'[Date]))

,[CUMILATIVE QTY]
)
)
                                                                      

Hope this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi @TomMartens / @MattAllington,

My data intially looks like below.

And Once I craeted Cumilative Qty measure as below data looks like below.

Cumilative Qty:= Cumilative Qty = CALCULATE(SUM('Fact'[QTY]),FILTER(ALL('Date'),'Date'[DATE]<=MAX('Date'[DATE])))

In the above screenshot the records marked in yellow are cumilative Qtys for dates that doesn't exist in main Fact table.

Then on top of the Cumilative Qty measure ned to create a measure to pick minimum of cumilative Qty of last 7 days from selected date as shown as in below screenshot. 

So, If you see in the above screenshot. I was taking minimum of cumilative Qty in last 7 days from the selection date.

For example, for  date 20170108 the least Cumilative Qty in last 7 days from 20170101 to 20170101 is on 20170102 , which is 620. The measue which y0u provided isn't working as expected.

Please let me know if required anything else.

[Anonymous]

Hi All,

I have tables with below structure.

Date	QTY
20th Oct 2017	300
21st Oct 2017	500
22nd Oct 2017	-200
23rd Oct 2017	100
24th Oct 2017	540
25th Oct 2017	-600
26th Oct 2017	300
27th Oct 2017	250

And I created a measure for running total of the Qty using the below dax query.

Cumilative Qty:= Calculate(SUM(QTY),

                                           Filter(ALL(DATE[DATE]),DATE[DATE key]<= MAX(Date[Date Key])

                                           )

And the Table with the measure next to it looks like below.

Date	QTY	Cumilative Sum
20th Oct 2017	300	300
21st Oct 2017	500	800
22nd Oct 2017	-200	600
23rd Oct 2017	100	700
24th Oct 2017	540	1240
25th Oct 2017	-600	640
26th Oct 2017	300	940
27th Oct 2017	250	1190

Now I want to get Minimum value of Cumilative Qty  in last 1 year from above table.

I kind of wrote the DA measure as below but it didn't work as the MIN function only accepts column reference and not Measures.

Min Cumilative QTy:= Calculate(MIN(Cumilative Qty),

                                                  DATESBETWEEN('Date'[Date],FIRSTDATE(DATEADD('Date'[Date],-1,YEAR)),

                                                                              LASTDATE('Date'[Date])

                                                   )

Kindly someone help me with this.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could refer to below steps:

Create a calender table:

Calender = CALENDARAUTO()

Create a measure:

Measure = CALCULATE(SUM(Table1[QTY]),FILTER(ALL('Table1'),'Table1'[Date]<=MAX('Table1'[Date])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

HI @MattAllington,

I tried the measue you gave but that isn't working when fact and data have many to one relation.

Please check the PBIX file and Excel data source in the below path.

Kindly take a look and let me know if we can do anythinh here.

https://drive.google.com/open?id=1f_kS6d8slskTs3qDOzBeCHCu8qM3oFIu

[MattAllington]

---

@Anonymous wrote:

HI @MattAllington,

I tried the measue you gave but that isn't working when fact and data have many to one relation.

Please check the PBIX file and Excel data source in the below path.

Kindly take a look and let me know if we can do anythinh here.

https://drive.google.com/open?id=1f_kS6d8slskTs3qDOzBeCHCu8qM3oFIu

---

I opened your file and added the original forumla I posted earlier. 

Min Qty of last 7 days = CALCULATE(MIN('Fact'[QTY]),DATESINPERIOD('Date'[DATE],MAX('Date'[DATE]),-7,DAY))

I then added the Calendar Date to the visual and this is what I got

To me, this is working correctly - it shows the minium quantity over the last 7 days.  However if you want the minimum daily quantity, then you will need a different solution.  This should do it

Min Cumilative Qty of last 7 days = CALCULATE(MINX('Date',CALCULATE(SUM('Fact'[QTY]))),DATESINPERIOD('Date'[DATE],MAX('Date'[DATE]),-7,DAY))

[Anonymous]

Hi @v-danhe-msft,

Thank You for your time.

Yeah your solution is working when there is one to one relation between Fact and Calendar tables.

But it is not working for my issue, where fact and calendar ave many to one relation.

I have uploaded the excel data source and PBIX file in below path.

Can you please have a look.

https://drive.google.com/open?id=1f_kS6d8slskTs3qDOzBeCHCu8qM3oFIu

Thanks,

Sailendra

[MattAllington]

First you need a Power BI Calendar Table.  Then you can write something like this

=CALCULATE (
min ( data[QTY] ),DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-7,DAY))