Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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 ?
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)
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
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
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
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 )
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 |
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])
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
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
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
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |