topic Re: Measure to show latest known data in DAX Commands and Tips

Measure to show latest known data

Teun1990 — Wed, 04 May 2022 08:42:29 GMT

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

Re: Measure to show latest known data

v-chenwuz-msft — Mon, 09 May 2022 02:07:03 GMT

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.

Re: Measure to show latest known data

Teun1990 — Mon, 09 May 2022 15:29:37 GMT

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

Re: Measure to show latest known data

v-chenwuz-msft — Tue, 10 May 2022 04:55:12 GMT

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.

Re: Measure to show latest known data

Teun1990 — Tue, 10 May 2022 06:53:36 GMT

Thank you!! - this works 🙂