Reply
Frequent Visitor
Posts: 11
Registered: ‎12-17-2018

Capture Min value of Last 12 Months

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 Smiley Sad

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:

Min Value.PNG

This is my Max calculation:

Max Quarters Avg Fac ID =
CALCULATE(([Avg Facilities Count 2]),
FILTER( ALL(DateSelector[YearMonth]),
DATEDIFF(DATE(YEAR(MAX(DateSelector[YearMonth])),MONTH(max(DateSelector[YearMonth])),DAY(DateSelector[YearMonth])), DateSelector[YearMonth] ,QUARTER) <=0 && DATEDIFF(DATE(YEAR(MAX(DateSelector[YearMonth])),MONTH(MAX(DateSelector[YearMonth])),DAY(DateSelector[YearMonth])), DateSelector[YearMonth] ,QUARTER) >= 0 ))
 
 

Thank you so much in advance!

 

Established Member
Posts: 201
Registered: ‎07-12-2017

Re: Capture Min value of Last 12 Months

Hi @ssaz

 

Please share some base data , this will help to get to the solution fast.

 

Regards

Affan

Frequent Visitor
Posts: 11
Registered: ‎12-17-2018

Re: Capture Min value of Last 12 Months

Hi,

 

Here is the attachment:

Link

 

Thanks.

Community Support Team
Posts: 2,494
Registered: ‎02-06-2018

Re: Capture Min value of Last 12 Months

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

 

Frequent Visitor
Posts: 11
Registered: ‎12-17-2018

Re: Capture Min value of Last 12 Months

@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. Smiley Happy

Community Support Team
Posts: 2,494
Registered: ‎02-06-2018

Re: Capture Min value of Last 12 Months

@ssaz,

 

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

Frequent Visitor
Posts: 11
Registered: ‎12-17-2018

Re: Capture Min value of Last 12 Months

@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

Community Support Team
Posts: 2,494
Registered: ‎02-06-2018

Re: Capture Min value of Last 12 Months

@ssaz,

 

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 

Highlighted
Frequent Visitor
Posts: 11
Registered: ‎12-17-2018

Re: Capture Min value of Last 12 Months

@v-yuta-msft


Hi thanks for the reply, unfortunately the formula still doesnt work.
minmax.PNG

The highlighted in yellow is your calculation, it still showing the max quarter number instead of showing 1896 as my max value.