cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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),

,LASTDATE('DimDate'[Date]))

Please do the needful.

8 REPLIES 8
Frequent Visitor

## How to write a DAX measure to get Minimum qty between dates from Cumulative sum measure?

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),

LASTDATE('Date'[Date])

)

Kindly someone help me with this.

Super User

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

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Support Team

## Re: How to write a DAX measure to get Minimum qty between dates from Cumulative sum measure?

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: How to write a DAX measure to get Minimum qty between dates from Cumulative sum measure?

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.

Thanks,

Sailendra

Frequent Visitor

## Re: How to write a DAX measure to get Minimum qty between dates from Cumulative sum measure?

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.

Super User

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

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]
,LASTDATE('DimDate'[Date]))

,[CUMILATIVE QTY]
)
)
```

Hope this is what you are looking for.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User

## Re: How to write a DAX measure to get Minimum qty between dates from Cumulative sum measure?

@sailendra1990 wrote:

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.

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor

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

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.