Forum Discussion
Actual vs Target
Hi,johnt75 and tomorrowyw .thanks for your concern about this issue.
Your answers are excellent!
And I would like to share some additional solutions below.
Hello,karthik_donthi.I am glad to help you.
johnt75 and tomorrowyw's suggestion is very good, creating a calendar table and creating joins will do what you need: calculate the number of Orders for each year.
Here is my test data.
01.01.2023 as date type
If you have real data, the Date column is not a date type, but a text type, it doesn't affect the final result.
You just need to use the left/right function with the find function to get the valid data of the date, and then use the date function to recreate the corresponding date column (using the dax code).
Using Power Query for string splicing, you can achieve the same effect: create a new date column.
1. Data conversion, the date column of the data in the ‘.’ Replace ‘.’ with ‘/’ in the data in the date column, and then convert it to date type in Power Query, and if there is an error, you can also do the type conversion in Power BI.
Convert type to date
Create a calendar table and add a column YearNum
Create measure:
M_YearOrderNum =
CALCULATE (
COUNT ( 'YearsOrder'[OrderID] ),
FILTER (
ALL ( 'YearsOrder' ),
RELATED ( CalendarTable[YearNumber] ) = MAX ( 'CalendarTable'[YearNumber] )
)
)
I've shared the pbix file for the test, I hope it helps!
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.