cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FrankWoody
Helper I
Helper I

FILTER AND COLUMN[HELP]

I have this base table that updates weekly the Last update column indicates the last update of the projects.
Project_Id is a unique key for each project
base_data is the project start date
data project_end is the deadline for the project to be completed.

Project_IdNOTESData_baseData_ProjectEendLAST UPDATE
0a887f68Traveled to NY24/10/202225/03/202303/01/2022
0a8881acTraveled to BO25/10/202226/03/202303/01/2022
0a8882b0Traveled to NI26/10/202227/03/202303/01/2022
0a8883a0Traveled to EM27/10/202228/03/202303/01/2022
0a88849aTraveled to CL28/10/202229/03/202303/01/2022
0a88858aTraveled to PL29/10/202230/03/202303/01/2022
0a88868eTraveled to SS30/10/202231/03/202303/01/2022
0a88877eTraveled to WE31/10/202201/04/202303/01/2022
0a888b0cTraveled to AF01/11/202202/04/202303/01/2022
0a888c10Traveled to OO02/11/202203/04/202303/01/2022
0a887f68Traveled to NY24/10/202225/03/202304/03/2022
0a8881acTraveled to BO25/10/202201/04/202304/03/2022
0a8882b0Traveled to NI26/10/202230/03/202304/03/2022
0a8883a0Traveled to EM27/10/202228/06/202304/03/2022
0a88849aTraveled to CL28/10/202229/10/202304/03/2022
0a88858aTraveled to PL29/10/202230/03/202304/03/2022
0a88868eTraveled to SS30/10/202231/03/202304/03/2022
0a88877eTraveled to WE31/10/202201/04/202304/03/2022
0a888b0cTraveled to AF01/11/202202/05/202304/03/2022
0a888c10Traveled to OO02/11/202203/04/202304/03/2022



The thing is I have some smart guys who like to change this project_end date so when that happens I would like bi to show up the project_end data and count how many times they were changed and show a summary sheet like this, the status would show how many times the project_end was changed this could help to create a conditional formatting green "OK" and red"It would indicate a change and I would see how many times it changed". 

@tamerj1 Challenge of Challenges 

@SpartaBI 

@Vijay_A_Verma

 

Project_IdNOTESData_baseData_ProjectEendSTATUS 
0a887f68Traveled to NY24/10/202225/03/20230
0a8881acTraveled to BO25/10/202201/04/20231
0a8882b0Traveled to NI26/10/202230/03/20231
0a8883a0Traveled to EM27/10/202228/06/20231
0a88849aTraveled to CL28/10/202229/10/20231
0a88858aTraveled to PL29/10/202230/03/20230
0a88868eTraveled to SS30/10/202231/03/20230
0a88877eTraveled to WE31/10/202201/04/20230
0a888b0cTraveled to AF01/11/202202/05/20231
0a888c10Traveled to OO02/11/202203/04/20230
2 ACCEPTED SOLUTIONS

@FrankWoody 
Like this? https://www.dropbox.com/t/jCppbj0iG8WHnqyU

1.png

Or you need to create a new calculated table?

View solution in original post

Hi @FrankWoody 

https://www.dropbox.com/t/41Hy38PGp5cLi5CK

In this case you can modify your measure to count the number of changes up to the selected channge date. So if you want to see wht was the status of your data at any previous week you just select that week and see how many changes where there.

1.png2.png

Number of Changes = 
COUNTROWS ( 
    CALCULATETABLE ( 
        project_end,
        project_end[LAST UPDATE] <= MAX ( project_end[LAST UPDATE] )
    )
) - 1

View solution in original post

7 REPLIES 7
FrankWoody
Helper I
Helper I

How can I Right it in DAX. ? 

@FrankWoody 
Like this? https://www.dropbox.com/t/jCppbj0iG8WHnqyU

1.png

Or you need to create a new calculated table?

My idea is to pull the information straight from the MS planner, my activity is to check if the managers are not changing the Data_ProjectEend, this way I can follow how many times they changed the final date my problem is that the BI does not store the history because every time it updates directly from the data source it overwrites the Data_ProjectEend and I ended up losing the previous date if it changes.

Hi @FrankWoody 

add a new measure to the table 

Number of changes =

COUNTROWS ( TableName ) - 1

01.png
Yes that worked!!
My problem is my database, for example, in the original there are only 10 lines that would update its status. but for testing purposes I doubled it to 20 lines and changed some dates to simulate the changes.
would I be able to store the information in the BI, to apply this comparison without them being overwritten.
For example this week I would automatically update the BI directly from the web, and next week when I would update the Bi I would store the data from this table to compare with the next update. Did you understand? [

Hi @FrankWoody 

https://www.dropbox.com/t/41Hy38PGp5cLi5CK

In this case you can modify your measure to count the number of changes up to the selected channge date. So if you want to see wht was the status of your data at any previous week you just select that week and see how many changes where there.

1.png2.png

Number of Changes = 
COUNTROWS ( 
    CALCULATETABLE ( 
        project_end,
        project_end[LAST UPDATE] <= MAX ( project_end[LAST UPDATE] )
    )
) - 1
tamerj1
Super User
Super User

Hi @FrankWoody 

not sure if I correctly understand but this should be simple. Just place the ID in the table visual then add other columns as measures. All of them can just SELECTEDVALUE except the end date should be MAX. the new column is just COUNTROWS ( Table ) -1

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors