Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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. 🙂
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |