cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sailendra1990 Frequent Visitor
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.

 

DateQTY
20th Oct 2017300
21st Oct 2017500
22nd Oct 2017-200
23rd Oct 2017100
24th Oct 2017540
25th Oct 2017-600
26th Oct 2017300
27th Oct 2017250

 

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.

 

DateQTYCumilative Sum
20th Oct 2017300300
21st Oct 2017500800
22nd Oct 2017-200600
23rd Oct 2017100700
24th Oct 20175401240
25th Oct 2017-600640
26th Oct 2017300940
27th Oct 20172501190

 

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.

8 REPLIES 8
sailendra1990 Frequent Visitor
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.

DateQTY
20th Oct 2017300
21st Oct 2017500
22nd Oct 2017-200
23rd Oct 2017100
24th Oct 2017540
25th Oct 2017-600
26th Oct 2017300
27th Oct 2017250

 

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.

DateQTYCumilative Sum
20th Oct 2017300300
21st Oct 2017500800
22nd Oct 2017-200600
23rd Oct 2017100700
24th Oct 20175401240
25th Oct 2017-600640
26th Oct 2017300940
27th Oct 20172501190

 

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.

Super User
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
Community Support Team

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

Hi @sailendra1990,

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:

1.PNG

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.
sailendra1990 Frequent Visitor
Frequent Visitor

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

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

 

sailendra1990 Frequent Visitor
Frequent Visitor

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

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

Super User
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]
,FIRSTDATE(DATEADD('DimDate'[Date],-1,YEAR))
,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
Super User

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


@sailendra1990 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

 

image.png

 

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.
sailendra1990 Frequent Visitor
Frequent Visitor

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

Hi @TomMartens / @MattAllington,

My data intially looks like below.

1initial.PNG

 

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

2cumilative Qty.PNG

 

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. 

 

 

3.PNG

 

 

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.