Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
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.
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
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
@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))
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
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |