cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kirti_agarwal28
Frequent Visitor

dax formula for selecting sum of Id where date is latest

Hi All,

 

I am new user of Power BI and  need a help in writing DAX Query for one measure:

 

input data:

ID values Date
1 2 1-Oct
1 2 1-sept
1 3 1-Aug
2 5 1-Aug
2 6 1-sept
2 7 1-July
3 2 1-July
3 1 1-June


Result:
ID values Date
1      2     1-Oct
2      6     1-sept
3      2     1-July

 

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User IV
Super User IV

@kirti_agarwal28 

 

Create the following measure and assign it to the table visual filter and set it equal to 1.

 

 

Flag = 
VAR _DATE = 
    CALCULATE(
        MAX(Table11[DATE]),
        ALLEXCEPT(Table11,Table11[ID])
    )
VAR _VALUE = 
    CALCULATE(
        MAX(Table11[VALUE]),
        Table11[DATE] = _DATE,
        ALLEXCEPT(Table11,Table11[ID])
    )
RETURN
IF( SELECTEDVALUE(Table11[DATE]) = _DATE && SELECTEDVALUE(Table11[VALUE]) = _VALUE,
    1,
    0
)

 

Screenshot 2020-11-05 182136.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

4 REPLIES 4
kirti_agarwal28
Frequent Visitor

Thanks @Fowmy 

daxer
Solution Sage
Solution Sage

 

// This measure will show you the latest
// value if only one ID is visible in
// the current context. For this to work
// there can't be duplicated dates within
// one and the same ID, which is the case
// in your test data. If this is not true,
// then you won't be able to pick a unique
// value.
[Latest Value] =
IF( HASONEFILTER( T[ID] ),
    MAXX(
        topn(1,
            T,
            T[Date], // must be of the date(time) type
            DESC
        ),
        T[Value]
    )
)

// The second accompanying measure.
[Latest Date] =
IF( HASONEFILTER( T[ID] ),
    MAXX(
        topn(1,
            T,
            T[Date], // must be of the date(time) type
            DESC
        ),
        T[Date]
    )
)

To see this working just drop ID's on the canvas and then the measures.

 

amitchandak
Super User IV
Super User IV

@kirti_agarwal28 , Create these two measures and use with ID

max Date = max(Table[Date])
latest value = lastnonblankvalue(Table[Date],max(Table[values]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Fowmy
Super User IV
Super User IV

@kirti_agarwal28 

 

Create the following measure and assign it to the table visual filter and set it equal to 1.

 

 

Flag = 
VAR _DATE = 
    CALCULATE(
        MAX(Table11[DATE]),
        ALLEXCEPT(Table11,Table11[ID])
    )
VAR _VALUE = 
    CALCULATE(
        MAX(Table11[VALUE]),
        Table11[DATE] = _DATE,
        ALLEXCEPT(Table11,Table11[ID])
    )
RETURN
IF( SELECTEDVALUE(Table11[DATE]) = _DATE && SELECTEDVALUE(Table11[VALUE]) = _VALUE,
    1,
    0
)

 

Screenshot 2020-11-05 182136.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors