How to get latest Status in Month

[vikashluv143]

I have 1 table in which vehicle ID , Status , Date Columns. 
Status ( SIgnup , Enrolled , Activated ), Vehicle ID is unique ..

Suppose 1 user (Vehicle ID) first Signup in App , then Enrolled , then Activated.

But If any status is changed within Month then Show only Latest Status ( as per Date Filter ). (Not to display all status aganist one vehicle in month )

Can someone help on this with DAX ?

@Dangar332 

VEHICLE_ID	VEHICLE_ACTUAL_STATUS	UPDATED_DATE
201405	Signed_Up	21-Feb-23
201405	Activated	29-Jul-22
201405	Enrolled	15-Sep-23
201405	Transferred	29-Jul-22
201405	Activated	16-Sep-23

Output :- (seems like )
Month   Count of Status 
Sept-23    1            (count only for leatest update , in sept ENrolled & Activated both occured but need to count only latest one)
Feb-23      1 ( it has only one Signed up)

[Dangar332]

hi, @vikashluv143 

if you wnat to show latest status then 

for that you have to add index-column using power query editor (go to power query  and in  addcolumn section add index and  close and apply)

which look like these

use below code 

latest status =

var co =  COUNTROWS(FILTER('status',SELECTEDVALUE('status'[UPDATED_DATE])))

var st  = MAXX(FILTER('status', 'status'[Index]),'status'[VEHICLE_ACTUAL_STATUS])

return

if(ISINSCOPE('status'[UPDATED_DATE]),IF( co>=2,st,st),BLANK())

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Dangar332]

hi, @vikashluv143 

if you want count of latest update then it's always 1

because if there are 5 upadtes in one month and you want latest count then it's always 1.
for that you use below code 

Measure =

var co =  COUNTROWS(FILTER('status',SELECTEDVALUE('status'[UPDATED_DATE])))

return

IF( co>=2,1,co)

here status is yourtable name

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[vikashluv143]

No, This is not working ..
We want to filter on Month Basis ( Like I want to filter Sept-23 , then it will seems to show only " Activated status, but in Sept-23 "Enrolled, "Activated" both happended." ".

I am trying to plot on bar graph (X-axis keeping Month & Y axis keeping No of Users with latest status )

[vikashluv143]

@Dangar332 

VEHICLE_ID	VEHICLE_ACTUAL_STATUS	UPDATED_DATE
201405	Signed_Up	21-Feb-23
201405	Activated	29-Jul-22
201405	Enrolled	15-Sep-23
201405	Transferred	29-Jul-22
201405	Activated	16-Sep-23

[Dangar332]

Hi, @vikashluv143 
i use same data you provide

add column to table like these

these new column help us so we  can filter data month wise

create meausre to show only max date in particular month

measure2 = MAXX(FILTER('yourtable','yourtable'[Column]),'yourtable'[UPDATED_DATE])

create measure which show latest status of month

latest status = MinX(FILTER('status','status'[Column]),'status'[VEHICLE_ACTUAL_STATUS])

put two measure in table 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[vikashluv143]

please send your gmail or ping me on viku10is@gmail.com , I will send you data .. This is not getting what we want 

[v-yiruan-msft]

Hi @vikashluv143 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below to get the latest status

Latest status = 
VAR _month =
    SELECTEDVALUE ( 'Table'[UPDATED_DATE].[MonthNo] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[UPDATED_DATE] ),
        FILTER ( ALLSELECTED ( 'Table' ), MONTH ( 'Table'[UPDATED_DATE] ) = _month )
    )
VAR _status =
    CALCULATE (
        MAX ( 'Table'[VEHICLE_ACTUAL_STATUS] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[UPDATED_DATE] = _maxdate )
    )
RETURN
    _status

2.Put the measure onto the Tooltips option

If the problem still not be resolved, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[vikashluv143]

please ping me on viku10is@gmail.com , I will send you data 

[v-yiruan-msft]

Hi @vikashluv143 ，

You can refer the following links to share some mockup data(exclude sensitive data) or pbix file with us. And it's not allowed for us to go privately through other tools to discuss the content of a post.

How to provide sample data in the Power BI Forum

How to upload PBI in Community

Best Regards

[Dangar332]

hi, @vikashluv143 

 provide some sample data