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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
vikashluv143
Frequent Visitor

How to get latest Status in Month

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_IDVEHICLE_ACTUAL_STATUSUPDATED_DATE
201405Signed_Up21-Feb-23
201405Activated29-Jul-22
201405Enrolled15-Sep-23
201405Transferred29-Jul-22
201405Activated16-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)

10 REPLIES 10
Dangar332
Super User
Super User

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

Dangar332_2-1696410523939.png

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())
  
Dangar332_0-1696410360559.pngDangar332_1-1696410405185.png

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

Dangar332
Super User
Super User

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

Dangar332_1-1696408031475.png

 


 

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

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
Frequent Visitor

@Dangar332 

VEHICLE_IDVEHICLE_ACTUAL_STATUSUPDATED_DATE
201405Signed_Up21-Feb-23
201405Activated29-Jul-22
201405Enrolled15-Sep-23
201405Transferred29-Jul-22
201405Activated16-Sep-23

Hi, @vikashluv143 
i use same data you provide

 

add column to table like these

Dangar332_0-1696437202668.png

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 
Dangar332_0-1696440793378.png

 

Dangar332_2-1696437640578.png

 

Dangar332_1-1696440823392.png

 

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

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

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

vyiruanmsft_1-1698744198395.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dangar332
Super User
Super User

hi, @vikashluv143 

 provide some sample data 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.