Hi Team,
I have data where i want to calculate Year to date.
At any point of time when pulling the YTD data, each record’s latest Status should only be captured. No Double Counts(Only latest data needed for and till the time of that month)
| ID | Status | Date |
| 1 | Start | 01-01-2022 |
| 1 | Due | 05-02-2022 |
| 1 | Working | 11-03-2022 |
| 1 | Completed | 15-04-2022 |
| 1 | Closed | 04-05-2022 |
| 2 | Start | 01-02-2022 |
| 3 | Start | 01-02-2022 |
| 2 | Working | 01-03-2022 |
In the above table, the record 1 had a Status as Start in January with Date as 01-01-2022.
In February , it gets changed to Due on 05-02-2022,in March to Working on 11-03-2022,
in April to Completed on 15-04-2022 and in May, the record 1 is changes to Closed on 04-05-2022.
In the above table, the record 2 had a Status as Start in February Date as 01-01-2022. In March it gets changed to Working on 01-03-2022. It had remained Working till May.
In the above table, the record 3 had a Status as Start in February Date as 01-01-2022. It has not got changed and had remained Start till May.
Calculation and Visualization
Year to Date Data
| | Start | Due | Working | Completed | Closed |
| Jan | 1 | 0 | 0 | 0 | 0 |
| Feb | 2 | 1 | 0 | 0 | 0 |
| Mar | 1 | 0 | 2 | 0 | 0 |
| Apr | 1 | 0 | 1 | 1 | 0 |
| May | 1 | 0 | 1 | 0 | 1 |
| | | | | | |
| Final YTD Counts(May) | 1 | 0 | 1 | 0 | 1 |
In the above table, suppose we are pulling the count on YTD basis:
YTD January: It would show the count only as 1 for Start of 1, rest of the Status would have the count as 0.
YTD February: The count of Start would be 2 which would be for ID 2 ,3 since these 2 were recorded as Start in February. The count of Due would be 1 which will be for 1 which got changed from Start to Due in February.
YTD March: The count of Start would be 1 which would be for 3 which gets it’s Status as Start carried forward from February and since 3 didn’t change it’s Status, it’s counted as Start till March yearly data. The count of Working would be 2 as 1 has changed from Due to Working in March and 2 changed from Start to Working. The other counts would be 0.
YTD April: The count of Start would be 1 which would be for 3 which gets it’s Status as Start carried forward from February and since 3 didn’t change it’s Status(in March or April), it’s counted as Start till April yearly data. The count of Working would be 1 which would be for 2(previous Status getting carried as it didn’t change in April). The count of Completed would be 1 as 1 has changed from Working to Completed in April.
YTD May: The final yearly count in May would be as: The count of Closed would be 1 as 1 has changed from Complete to Closed in May. The count of Working would be 1 which would be for 2(previous Status getting carried as it didn’t change in April/May). The count of Start would be 1 which would be for 3 (previous classification getting carried as it didn’t change in March/April/May).
How to implement the slicer/filter so that it can filter the data less than that month to start of that year i.e January.
