YTD there is one month, but Last YTD there are two months, messing up YoY% change

[MuniqueCrespo]

Hello, 

I am trying to solve an issue to my YTD, LYTD measures but wasn't able to find an answer to solve it.

Fiscal Year	YTD Revenue	Last YTD Revenue	YoY Rev %Change
2021	$600.00	$520.00	15%
2022	$1,200.00	$600.00	100%
2023	$300.00	$240.00	25%
May	$300.00	$120.00	150%
June		$240.00	-100%
TOTAL	$300.00	$240.00	25%

My desire is that when data is collapsed to show only Fiscal Year Totals, the YoY% change, LYTD would show only May's data, since there is no data on June F2023 for YTD Revenue.

I searched for many hours for the answer here at the forum but wasn't able to find an answer. I was able to use the logic to remove the -100% for June and the $240 from June since there is no data on Total Revenue YTD, but still the total for F2023 is still showing "25%" instead of "150%. 

Desired :
Fiscal Year	YTD Revenue	Last YTD Revenue	YoY Rev %Change
2021	$600.00	$520.00	15%
2022	$1,200.00	$600.00	100%
2023	$300.00	$120.00	150%

here are the formulas I have : 

Last YTD Revenue = if(isblank([Total Revenue]),blank(),CALCULATE([Total Revenue],DATESYTD(dateadd('dCalendar'[Calendar Date],-1,Year),"04/30")))

Total Revenue YTD = if(ISBLANK([Total Revenue]), Blank(), CALCULATE([Total Revenue],DATESYTD(dCalendar[calendar Date],"30/04")))

YoY Rev %Change = 
IF(
	ISFILTERED('dCalendar'[Calendar 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 Revenue YTD],
			DATEADD('dCalendar'[Calendar Date], -1, YEAR)
		)
	RETURN
		if(ISBLANK([Total Revenue]), Blank(), DIVIDE([Total Revenue YTD] - __PREV_Year, __PREV_Year)
))

[v-shex-msft]

HI @MuniqueCrespo,

I'd like to suggest you use the date function instead of the time intelligence functions, they should be more suitable for customized and nested use:

Time Intelligence "The Hard Way" (TITHW) - Microsoft Power BI Community

Regards,

Xiaoxin Sheng

[MuniqueCrespo]

hello @v-shex-msft , 

I tried the formula suggested but still, Total Last YTD is showing more months than YTD's. For example, YTD has data up to May 31,2022. But Last YTD is summing data until July ( since we are in July now). I want the LYTD Totals to follow YTD month-end so I can compare Apples to Apples. Once June's YTD is released and included to the data, THEN LastYTD would also show May-June totals. 

I realized my dcalendar table has max date to be Today's date. I believe this is the reason my LastYTD is following the Max date in the calendar table to be Today's date. So it includes two extra months to the LASTYTD column. As mentioned, YTD is capturing monthly data that is not daily released. Its only a few days after month-end that I receive the Month-end revenue report. 

Having that said, How can I force my dcalendar table date to have "last date" to be last period-end on the report, not today's date? 

Thanks,

[MuniqueCrespo · Accepted solution]

Hi Everyone, 

I was able to find a solution to this issue. 

I just needed to create a calculated column for my calendar table using DAX. 

The Date[DateWithSales] column is TRUE if the date is on or before the last date with sales; it is FALSE otherwise. In other words, DateWithSales is TRUE for “past” dates and FALSE for “future” dates, where “past” and “future” are relative to the last date with sales.

DateWithSales =
'Date'[Date] <= MAX ( Sales[Order Date] )

 found my solution here:

https://www.daxpatterns.com/week-related-calculations/ 

Thanks, 

Munique