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

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.

Reply
ArpitVijay
New Member

Return Value by max date where value is not equal to value of max date by dimension.

I would like to return "Value2" by max date and by Category "Name"

and then return the respective value from column "Value1"

 

I also want to compare the Value2 column max date values and return

the non-similar values by the max date concept by category.

and then return the respective value from column "Value1"

 

 

ArpitVijay_0-1690145547956.png   

The output should be like this

 

ArpitVijay_2-1690146257185.png

 

 

NameDDMMYYValue1Vaue2
A1/1/202352
A1/2/202352
A1/3/202352
A1/4/202363
A1/5/202385
A1/6/202386
A1/7/202386
B1/1/202373
B1/2/202373
B1/3/202373
B1/4/202384
B1/5/2023106
B1/6/2023107
B1/7/2023107
1 ACCEPTED SOLUTION

6 REPLIES 6
tamerj1
Super User
Super User

Hi @ArpitVijay 
It is nor very clear and the sample data might be a little misleading. Howeve, please refer to attached sample file with the proposed solution

1.png

Value X = 
SUMX ( 
    VALUES ( 'Table'[Name] ),
    MAXX ( 
        TOPN ( 
            1, 
            CALCULATETABLE ( 'Table' ),
            'Table'[DDMMYY]
        ),
        'Table'[Value2]
    )
)
Value Y = 
SUMX ( 
    VALUES ( 'Table'[Name] ),
    MAXX ( 
        TOPN (
            1,
            TOPN ( 
                1, 
                CALCULATETABLE ( 'Table' ),
                'Table'[Value1]
            ),
            'Table'[DDMMYY],
            ASC
        ),
        'Table'[Value2]
    )
)

 

 

@tamerj1 

Value1 is right however Value2 can't be calculated by top n I think.

ArpitVijay_0-1690212909710.png

 

Max date by category is 1.6.2023
and the "Value X" for Category a is 6 and category b is 4 colored in orange
now I would like to find the Value Y based on Max date by category and
the value should not equal to Previous Value "Value X"

Value1 is right however Value2 can't be calculated by top n I think.

wiz_lamp_0-1690208110428.png

Max date by category is 1.6.2023
and the "Value X" for Category a is 6 and category b is 4 colored in orange
now I would like to find the Value Y based on Max date by category and
the value should not equal to Previous Value "Value X"

Manoj_Nair
Solution Supplier
Solution Supplier

@ArpitVijay- Please take a look at this DAX measure and let me know if it works for you. I'm uncertain if the results displayed by you are accurately calculated. Do let me know if there's something I might have overlooked.

 

VALUE X = 
VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table'))
VAR MaxValue2BeforeLastDate = CALCULATE(MAX('Table'[Vaue2]), 'Table'[DDMMYY] <> MaxDate)
RETURN MaxValue2BeforeLastDate

VALUE Y = 
VAR MaxDate = CALCULATE(MAX('Table'[DDMMYY]), ALL('Table'))
VAR MaxValue1BeforeLastDate = CALCULATE(MAX('Table'[Value1]), 'Table'[DDMMYY] <> MaxDate)
RETURN MaxValue1BeforeLastDate

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors