Year to Date and Year over Year

[Anonymous]

I'm creating a sales report and am trying to filter out year to date sales as well as year over year sales. My data table goes from 2020-2024 up until this month of June. 

I want to calculate the YTD totals for the previous years and compare them year over year. The current year (2024) already is YTD since there is no future/forecasting data, however my previous years have values from Jan-Dec.

I've made a [Total Revenue] measure that calculates the SUM of revenue of all the data in my table. But I want a measure that calculates the total revenue for all the years, but up until the YTD (June). I've tried to create a [YTD Revenue], [Revenue YTD Prior Year], and  a [YoY Revenue YTD] measure to show the values in both a table and card visual but it is still registering the full year's revenue data on previous years.

I've been trying to check my values and DAX functions in a table visual to see if everything is working properly. But the only way I have been able to "get around" this issue is by adding filters on the page that tell the visuals to not include the months Jul-Dec. However this doesn't feel very dynamic and I know it could create problems for future visualization.

Here are some screenshots of what my visuals look like (My report by the product categories but changed it to month so you can see what is happening): 

This is with no selection on the slicer, and the values are totaling up properly in the [Total Revenue] but in the [YTD Revenue] it is totaling up to what the 2024 total revenue would be (because 2024 data is already "YTD").

This is when I select the year 2024, and as [Total Revenue] is doing, there is no revenue for the months Jul-Dec. But in the prior years columns, they are still giving back values for the additional months. I expect this in my [Prior Year Revenue] measure, but not my [Revenue YTD Prior Year Measure]. 

And lastly, when I select 2023, my YTD measures are still giving me back values for the entire year, when I just want up until June.  

I've tried to change th DAX code to multiple different things but nothing seems to work the way I need it to. I don't know how to exactly explain the problem I am facing or if that makes sense but any guidance will help!

[hnguy71]

Hi @Anonymous ,

Firstly you'll need to find out the max period or month of the latest year. Something similar to this:

VAR _LatestDate = CALCULATE(MAX(DATE_TBL[Date]), ALL(DATE_TBL))
VAR _LatestMonth = MONTH(_LatestDate)

 
Then adjust your YoY measure to only show months up to your latest month number. Something similar to this:

CALCULATE([Revenue], DATE_TBL[Month] <= _LatestMonth)

[Anonymous]

I'm going to be honest I'm a rookie when it comes to DAX and Power Bi. I know how to get to certain results but understanding the code behind it I struggle with. Could you elaborate a bit more? 

[hnguy71]

Hi @Anonymous ,

The first portion of the code goes into your date table and returns the last date found within your model. Once that date is found, the second variable extracts the month number from the date. The ALL function strips away any filter context from your date table.

Once you have your latest month, regardless of year selected, you're going to use the year-over-year calculation that you currently have and then add an evaluation to only return the amounts up to that certain month number.

hope that helps.

[Anonymous]

Like this?

This [YoY Revenue] measure was my original measure 

YoY Revenue = 
DIVIDE( 
    [Total Revenue] - [Prior Year Revenue],
    [Prior Year Revenue])

Added these 2 measures

VAR Latest Date = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALL('Calendar'))

VAR Latest Month = MONTH([VAR Latest Date])

Updated [YoY] measure 

YoY = 
CALCULATE([Total Revenue], 'Calendar'[Month Name] <= [VAR Latest Month],
DIVIDE( 
    [Total Revenue] - [Prior Year Revenue],
    [Prior Year Revenue]))

  Is that correct?

[hnguy71]

Hi @Anonymous ,

Now that I see your measure. Replace your YoY Revenue measure with this and it should work:

YoY Revenue = 

VAR _LatestDate = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALL('Calendar')
)

VAR _LatestMonth = MONTH(_LatestDate)


RETURN

CALCULATE( 
	DIVIDE([Total Revenue] - [Prior Year Revenue], [Prior Year Revenue]), 
	Month('Calendar'[Date]) <= _LatestMonth
)

[Anonymous]

I'm not sure that made a difference as my other YTD measures still do not reflect data from the time frame Jan-Jun. It is still giving data of overall totals for all the months (Jan-Dec).  

I think I need to perfect my YTD before I handle the YoY. But I want my YTD to be applied to each year (Jan-Jun data for each year) and then can be filtered by year on a slicer. So show the total revenue of YTD (Jan-Jun) regardless of year chosen. And then be able to compare that YoY from the YTD time range.

[hnguy71 · Accepted solution]

@Anonymous ,

Does your YoY Revenue measure stop at June though?

EDIT: I have built out some of the base measures that is required for your success:

You can use a base measure for your "latest month" to be reused across all your other measures (adjust all accordingly to your model):

LatestMonth = 
VAR _LatestDate = CALCULATE(MAX('Date'[Date]), ALL('Date'))
VAR _LatestMonth = MONTH(_LatestDate)
RETURN
_LatestMonth

base total revenue measure:

Total Revenue = SUM(Sales[Sales Amount])

Revenue YTD:

Revenue YTD = 
VAR _LatestMonth = [LatestMonth]
RETURN
CALCULATE([Total Revenue], DATESYTD('Date'[Date]), FILTER('Date', 'Date'[MonthNum] <= _LatestMonth) )

Revenue YTD PY:

Revenue YTD PY = 
VAR _LatestMonth = [LatestMonth]
RETURN
CALCULATE([Revenue YTD], DATEADD('Date'[Date], -1, YEAR), FILTER(ALL('Date'), 'Date'[MonthNum] <= _LatestMonth) )

Revenue YoY %:

Revenue YoY % = 
VAR _CY = [Revenue YTD]
VAR _PY = [Revenue YTD PY]
RETURN
IF( NOT ISBLANK(_CY),  DIVIDE(_CY - _PY, _PY) )

My sample output where my latest month is June:

[Anonymous]

I swear I put in the code as you had it and adjusted based on my model but it is still not working for me. I feel like this is a relatively common thing businesses ask for but I can't figure out how to display it in Power Bi. 

When 2024 is selected in slicer, it the [Revenue YTD PY] is still showing results past June:

When 2023 is selected in slicer, still giving me results for months past June for all measures: 

With no slicer selection, it is still doing this: 

EDIT: wait I think it is working now!!! I needed to change the dates in my measure

 

[hnguy71]

nice! Glad to hear it~!