I need to calculate year-over-year (YOY) sales growth for my business, but some new sales channels were added in the current year that were not present in the previous year. I want to include these new channels in the YOY comparison and show their sales values as if they existed in the previous year. Specifically, I want to show the sales for these new channels as 100% when comparing to the previous year’s sales.
This is interesting, as there are a lot of ways to go at this, to get 100% you need to divide the country total by itself, the logic would be something like current year - previous year divided by the previous year plus any current year if it didnt sell in the previous year, that would in the case for the line items mean if it didnt sell in the previous year it would just divide current year by current year. At the grand total level this would calculate the difference between current year and previous year, then divide by previous year plus current year of items that didnt sell in the previous year, which doesnt really give a proper analysis as your inflating previous years values and only looking at the difference between the actuals. If you account for it at the grand total level then you need to be aware of the difference as it follows different logic.
I am not sure if this is what you were after, but it may give you a hint, or someone else who may be able to help answer your query, and it was fun trying
YTD YoY% =
IF(
ISFILTERED(‘Dates’[Date]),
ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.”),
VAR __PREV_YEAR = CALCULATE([Total Sales], Dates[IsPYTD] = TRUE)
VAR __CUR_YEAR_CHANNELS = CALCULATETABLE(SUMMARIZE(‘Full Sales Report’,‘Full Sales Report’[Country]),‘Dates’[IsCurrentFY] = True)
VAR __PREV_YEAR_CHANNELS = CALCULATETABLE(SUMMARIZE(‘Full Sales Report’,‘Full Sales Report’[Country]),‘Dates’[IsPYTD] = True)
VAR __NEW_CHANNELS = EXCEPT(__CUR_YEAR_CHANNELS,__PREV_YEAR_CHANNELS)
VAR __NEW_CHANNELS_SALES = CALCULATE( [Total Sales], KEEPFILTERS(Countries[Country] IN __NEW_CHANNELS) )
RETURN
IF( HASONEVALUE(‘Countries’[Country]), DIVIDE(CALCULATE([Total Sales], Dates[IsCurrentFY] = True) - __PREV_YEAR, __PREV_YEAR + __NEW_CHANNELS_SALES), DIVIDE(CALCULATE([Total Sales], Dates[IsCurrentFY] = True) - (__PREV_YEAR + __NEW_CHANNELS_SALES), __PREV_YEAR + __NEW_CHANNELS_SALES ))
)
thanks for your explain here, that’s what i need exactly, i tried your DAX Code, but still not working i will try to adjust trying to have the correct formula i will let you know if i succussed in that and i will share it if some one else can help it will be grateful thanx again kylie and have a good day
No problems at all, im sure someone can help resolve the issue now there are more details.
It would help if you could advise if there is an error and the measure doesnt display any results, or it displays the wrong results than what you were expecting, and if you can give an example.
This is the results I got below
I think the code pasted strangely and the " ’ " charated got replaced with single quotes, so will try to paste in differently
YTD YoY% =
IF( ISFILTERED('Dates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR = CALCULATE([Total Sales], Dates[IsPYTD] = TRUE)
VAR __CUR_YEAR_CHANNELS = CALCULATETABLE(SUMMARIZE('Full Sales Report','Full Sales Report'[Country]),'Dates'[IsCurrentFY] = True)
VAR __PREV_YEAR_CHANNELS = CALCULATETABLE(SUMMARIZE('Full Sales Report','Full Sales Report'[Country]),'Dates'[IsPYTD] = True)
VAR __NEW_CHANNELS = EXCEPT(__CUR_YEAR_CHANNELS,__PREV_YEAR_CHANNELS)
VAR __NEW_CHANNELS_SALES = CALCULATE( [Total Sales], KEEPFILTERS(Countries[Country] IN __NEW_CHANNELS) )
RETURN
IF( HASONEVALUE('Countries'[Country]), DIVIDE(CALCULATE([Total Sales], Dates[IsCurrentFY] = True) - __PREV_YEAR, __PREV_YEAR + __NEW_CHANNELS_SALES), DIVIDE(CALCULATE([Total Sales], Dates[IsCurrentFY] = True) - (__PREV_YEAR + __NEW_CHANNELS_SALES), __PREV_YEAR + __NEW_CHANNELS_SALES ))
)