Duplicate value for total after status change

[Anonymous]

Hi,

I have an issue with my report for total value (to-date). I'ts a simple measure, but it provides duplicate projects because a project has changed status. My table has status in a column, which means that a project with a history of more than one status, then it's going to show up on both statuses. I would like the total value to only show once per project and for the status it has in the selected period. Any suggestions on how to solve this?
I have created a sample pbix file which shows the problem, link here:

https://www.udrop.com/5jCK/Revtodateissue.pbix

Has you can see from the picture, project 1 and 2 shows up twice..

 Thanks!

[Anonymous · Accepted solution]

Hi,

Solved it by using this measure:

Revnue to date LastValue = 
    CALCULATE(
        [Revenue to date], 
        LASTNONBLANK(
            'histproject'[period], 
            [Revenue to date]
            )
)

[AllisonKennedy]

@Anonymous glad you solved it and thanks for the update. 

[AllisonKennedy]

@Anonymous 

Which status do you want the project to fall into? You already have Status column in your DimProject table, so use that in your visual instead of ProjHistory[Status] and that will solve your problem. If that's not the status you want, then please specify what you're looking for or why that doesn't work for your requirements. 

Cheers,

Allison

PS - I would suggest changing the relationship between DimProjects and ProjHistory to SINGLE cross-filter direction so that you don't accidentally get these duplicate tables in the future. 

[Anonymous]

Hi @AllisonKennedy , I agree that using status from my dimProject tabel will give med the correct latest status. This row status will change. My ProjHistory has the status a project was in for each period, which is what I want, because I would like to use a slicer for months and then see what status the project had in the selected month, if that makes sense. 

i.e, if I select period 202104, then project 1 should show up in status 2, but if I select period 202102, then I would like it to show up with status 1.