Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 😞

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!

 

8 REPLIES 8
v-yuta-msft
Community Support
Community Support

@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

Anonymous
Not applicable

@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 

Anonymous
Not applicable

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

affan
Solution Sage
Solution Sage

Hi @Anonymous

 

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

 

Regards

Affan

Anonymous
Not applicable

Hi,

 

Here is the attachment:

Link

 

Thanks.

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

 

Anonymous
Not applicable

@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. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.