Measure to show latest known data

[Teun1990]

Hi,

I have a dataset with some ID's, a status (accept/reject/ua) and a Uploadweek.

- ID's, Status, Accept, Uploadweek comes from tableA

- JaarWeek comes from table B 

Made a relationship between Jaarweek (consists all weeks) and Uploadweek (only weeks that have data)

I would like to create a table (& later a graph) which will show the amount of issues per week, per ID, per Accept/Reject category in a way that :

- if there are issues in that week for that ID, it shows that number. 

- if thats not the case it needs to take the latest known amount of issues. 

I feel like i'm halfway with my measure called _Measure1 but i'm not getting exactly what I need.

Is there anyone who can help me ? 

Please find below a visual representation of what i would like to achieve.

Kind Regards,

Teun

[v-chenwuz-msft]

Hi @Teun1990 ,

Maybe you can try this expression:

_measure1 =
IF (
    ISBLANK ( [_AmountOfIssues] ),
    CALCULATE (
        [_AmountOfIssues],
        FILTER ( 'Table', [Jaarweek] < MAX ( 'Table'[Jaarweek] ) )
    ),
    [_AmountOfIssues]
)

Please share your pbix file without sensitive data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

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

[Teun1990]

Hi _ chenwu zhu,

Thank you for your reply, unfortunately it was not the solution, but it did get me a bit closer to the solution (I think at least)

The totals dont add up correctly & the visual at the bottem left doesn't show the correct data.

Please find in RED cirkels the wrong data 

Do you have any idea?

Measure I created after your tip:

Measure3 =

VAR LastNonBlankDate =

CALCULATE (

MAX ( Date_Table[Date] ),

FILTER (

ALL ( Date_Table),

Date_Table[Date] <= MAX ( Date_Table[Date] )

&& Data1[_AmountOfIssues] <> 0

)

)

RETURN

CALCULATE (

Data1[_AmountOfIssues],

FILTER (ALL ( Date_Table), Date_Table[Date] = LastNonBlankDate )

)

For some reason I cannot attach a PBIX file to this post, is there an alternative way to share the file with you ? 

I hope this helps:

https://www.dropbox.com/s/mkajfmd8crzh325/Example1.pbix?dl=0 

Kind Regard, Teun

[v-chenwuz-msft · Accepted solution]

Hi @Teun1990 ,

I just modified your formula a little bit. Please see the formula below:

Measured =
IF (
    HASONEVALUE ( Date_Table[YearWeek] ),
    SUMX (
        SUMMARIZE (
            CALCULATETABLE (
                'Data1',
                FILTER (
                    ALLSELECTED ( Date_Table[YearWeek] ),
                    [YearWeek] <= MAX ( 'Date_Table'[YearWeek] )
                )
            ),
            [Teamcenter_Doc_ID],
            "SSS", [Measure3]
        ),
        [SSS]
    ),
    [_AmountOfIssues]
)

Best Regards

Community Support Team _ chenwu zhu

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

[Teun1990]

Thank you!! - this works 🙂