topic Re: How to get latest Status in Month in DAX Commands and Tips

How to get latest Status in Month

Anonymous — Wed, 04 Oct 2023 08:10:55 GMT

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)

Re: How to get latest Status in Month

Dangar332 — Wed, 04 Oct 2023 05:50:44 GMT

hi, Anonymous

provide some sample data

Re: How to get latest Status in Month

Anonymous — Wed, 04 Oct 2023 05:59:20 GMT

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

Re: How to get latest Status in Month

Dangar332 — Wed, 04 Oct 2023 08:27:24 GMT

hi, Anonymous

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.

Re: How to get latest Status in Month

Anonymous — Wed, 04 Oct 2023 08:55:22 GMT

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 )

Re: How to get latest Status in Month

Dangar332 — Wed, 04 Oct 2023 09:12:19 GMT

hi, Anonymous

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.

Re: How to get latest Status in Month

Dangar332 — Wed, 04 Oct 2023 17:33:51 GMT

Hi, Anonymous
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.

Re: How to get latest Status in Month

Anonymous — Thu, 05 Oct 2023 05:45:52 GMT

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

Re: How to get latest Status in Month

Anonymous — Tue, 31 Oct 2023 09:24:15 GMT

Hi Anonymous ,

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

Re: How to get latest Status in Month

Anonymous — Thu, 02 Nov 2023 11:59:40 GMT

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

Re: How to get latest Status in Month

Anonymous — Wed, 08 Nov 2023 05:13:13 GMT

Hi Anonymous ，

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