Forum Discussion
Helper ILatest entry for distinct values in Power BI
I need to get the latest entry for Ontrack for Cumulative column.
I tried get the latest entry by filtering the entries in achievement field by excluding the blank fields however this formula is only showing March as latest month.
Last Entry = CALCULATE(MAX('KPI'[Month]),FILTER('KPI','KPI'[Achieved]<>BLANK())).
As an example
For Deliverable:160, I need to see the Ontrack for Cumulative for latest entry which is in March - >OnTrack for **bleep**=4 & Latest Month should be March
For Deliverable:250, I need to see the Ontrack for Cumulative for latest entry which is in January- >OnTrack for **bleep**=0 & Latest month should be January
For Deliverable:264, I need to see the Ontrack for Cumulative for latest entry which is in February- >OnTrack for **bleep**=0 & Latest month should be February
Now with my calculation, for each deliverableID, latest entry is seen in MARCH.
Hi,
Is this the result you are expecting? You may download the file from here. Please note that i have removed the Status column. You had written a Status calculated column formula which in turn was refering a measure. A calculated column formula should not refer to a measure. If the result in the file is correct, then we can talk of the second problem i.e. getting the Status column in the Table with the help of a measure (not a calculated column).
9 Replies
- TomMartens
Super User
Hey,
it would be helpful if you create a pbix with sample data, upload the file to onedrive or dropbox and share the link.
I assume you create a calculated column?
You receive March for all you deliverables, because your current DAX takes not the single Deliverable into account. This could be done something like this
name of the column = CALCULATE( MAX('tablename'[Month]) ,FILTER( ALLEXCEPT('tablename', 'tablename'[DeliverableID]) ,'tablename'[Achieved] <> BLANK() )But you also have to keep in mind that MAX() from a string column returns March instead of April due to to alphabetically ordering.
I assume that you have to do the following
- Determine the MAX ID (assuming that the ID is a number that reflects a sequence and is unique across all deliverables) for each deliverable
- use the max ID to determine the value for the Last Entry column, maybe using the function LOOKUPVALUE()
https://msdn.microsoft.com/en-us/library/gg492170.aspx
If there is no sustainable Business Rule to determine the order of IDs, calculating a cumulative value will not be possible. You also might consider to transform the Month column in a real date value: 2018-02-01 represents February in the year 2018 and 2017-01-01 represents January in the year 2017
Hopefully this gives you an idea
Kind regards
Tom
ruyaselmanHi Tom,
Thanks for the response. My issue is also related to month field in original table. As you said its in string format.
I tried to get the order of months with a copy of KPI table, where I converted these months to date format. My original datasource is on Sharepoint, so I couldnt change the dates in Original Table. As a result I get a copy of this table, open in excel, and added two fields 2 show monts. 1-Date format 2-numbers from 1 to 12.
But I couldnt handle the max value with that way. When I want to put a filter on different table, I receive a circular dependency error.
What exactly I need, is to get the last Ontrack Cumulative value in this table.
So I thought, If I can get the latest entry for each distinct deliverable, I can sort out. However It wasnt successfull.
I uploaded the pbix example with example data on dropbox:
https://www.dropbox.com/s/wx3hcklr860c33s/Calculate%20Ontrack%20based%20on%20Last%20Entry.pbix?dl=0
- Ashish_Mathur
