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.
Hi All,
I need your help to solve this issue that I'm getting on to get the minimum value from last 12 months data. Now I have max but not min value 😞
Like the picture below, I need to get the value of 1664.00 which will always change prior to last 12 months data as I filter the date filter:
This is my Max calculation:
Thank you so much in advance!
@Anonymous,
DATE(YEAR(MAX(DateSelector[YearMonth])),MONTH(MAX(DateSelector[YearMonth])),DAY(DateSelector[YearMonth])), DateSelector[YearMonth] ,QUARTER) >= 0
Could you clarify about this logic in your MAX measure?
Regards,
Jimmy Tao
@v-yuta-msft
That formula is to get the max quarter value. but its not dynamic as I need a max formula that can always show max value prior the date filter changes.
For example if i filter November, then max value should show the quarter average of OCT & NOV, while when I filter December then the max value will show the quarter average of OCT,NOV & DEC.
Thank you.
Regards,
Sarah
@Anonymous,
Have you used selectedvalue(), allselected() to achieve the date value in your silicer?
Please try formula below:
Max Quarters Avg Fac ID = CALCULATE ( ( [Avg Count] ), FILTER ( ALL ( DateSelector[YearMonth] ), DATEDIFF ( DATE ( YEAR ( MIN ( DateSelector[YearMonth] ) ), MONTH ( MIN ( DateSelector[YearMonth] ) ), DAY ( DateSelector[YearMonth] ) ), SELECTEDVALUE ( DateSelector[YearMonth] ), QUARTER ) <= 0 && DATEDIFF ( DATE ( YEAR ( MIN ( DateSelector[YearMonth] ) ), MONTH ( MIN ( DateSelector[YearMonth] ) ), DAY ( DateSelector[YearMonth] ) ), SELECTEDVALUE ( DateSelector[YearMonth] ), QUARTER ) >= 0 ) )
Regards,
Jimmy Tao
@v-yuta-msft
Hi thanks for the reply, unfortunately the formula still doesnt work.
The highlighted in yellow is your calculation, it still showing the max quarter number instead of showing 1896 as my max value.
Hi @Anonymous
Please share some base data , this will help to get to the solution fast.
Regards
Affan
Hi ssaz,
Not very clear about the requirement "Minimun value should be Q4-17", change MAX with MIN and check if can meet your requirement:
Min Quarters Avg Fac ID = CALCULATE ( ( [Avg Count] ), FILTER ( ALL ( DateSelector[YearMonth] ), DATEDIFF ( DATE ( YEAR ( MIN ( DateSelector[YearMonth] ) ), MONTH ( MIN ( DateSelector[YearMonth] ) ), DAY ( DateSelector[YearMonth] ) ), DateSelector[YearMonth], QUARTER ) >= 0 && DATEDIFF ( DATE ( YEAR ( MIN ( DateSelector[YearMonth] ) ), MONTH ( MIN ( DateSelector[YearMonth] ) ), DAY ( DateSelector[YearMonth] ) ), DateSelector[YearMonth], QUARTER ) <= 0 ) )
Regards,
Jimmy Tao
@v-yuta-msft Hi, Thanks for the reply, the minimum doesnt have to Q4-17 always, all i need is the minimum and maximum number to be dynamic. I have attached the link in my previous reply to sample workbook for your further reference. Thanks. 🙂
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |