Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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).
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
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
Hi 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
Hi,
Is this the result you are expecting? You may download my PBI solution file from here.
Hi Ashish,
Thank you so much... This is what I`m looking for actually. However when I tried in original source, I couldnt sort it with that way.
You succesfully get the latest month , however I could be able to get the relevant month info for non-empty fields.
Now I`m adding the original source that I worked on; kindly check;
https://www.dropbox.com/s/o62aoapb871b76i/sample-2.pbix?dl=0
You have completed changed the question. In the original file you shared, there was a relationship from the KPIOrj table to the CopyKPI table - now there is no such relatioship. If your final relatioships are the ones that you have shown in your sample2 file, then there must be a date column in the KPI table.
Also, please share the final result you want.
Hi Ashish,
I checked the relation in the second file, I have a relation from Copy Kpi - Original KPI. I couldn`t get the point that you mentioned. Only names of tables are different in sample.
Should I have a relation from KPI - Copy Kpi? Does it make a difference if I switch them?
Actually my main problem was the month field in KPI table, which is string format.
Although today sharepoint developer added a new column in KPI Table, which is in date format, still in powerBi I can not convert this column to a date... PowerBi realize as a text... Because of this issue, I get a copy of KPI table and in excel manually I added a month column in date format. With that way, PowerBI understand as date. Strange..
As a second option, I wanted to add a column in KPI Table, however I couldnt replace January with a date.
Maximum point that I reached is to replace January with numbers like 1-2-3. Do you have a suggestion for converting to date?
As a method I tried,
Measure =SWITCH(KPI[MonthNumber],1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
But this is not helping me to sort the months properly in the table. I also tried to switch dates with mmddyyyy format, but it didnt work....
In the example that I sent to you, my main aim is to get the latest OnTrack Value...So what I thought, If I can get the latest entry for each ID like 160, 164 etc based on the last entered achievement, I can find out the corresponding On Track Value. And make my calculations.
Kindly find below example output;
Many thanks for the support
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).
Hi Ashish,
This is what I was looking for thank you so much..
I also find a way to add a new column and switch it to date format. Maybe it will be usefull for end users, I used the following code:
Column = SWITCH('KPI'[Month],"January",DATE(2018,01,01),"February",DATE(2018,02,01),"March",DATE(2018,03,01),"April",DATE(2018,04,01),"May",DATE(2018,05,01),"June",DATE(2018,06,01),"July",DATE(2018,07,01),"August",DATE(2018,08,01),"September",DATE(2018,09,01),"October",DATE(2018,10,01),"November",DATE(2018,11,01),"December",DATE(2018,12,01))
Many thanks for your support
You are welcome.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |