DAX - YTD with year on columns

Question

Can anybody suggest how to write YTD Sales formula in DAX that can be used (e.g. in Excel Pivot Table) with Year on column, to compare YTD sales over the last few years.

Specifically, my difficulty is in determining "as of date". If I refer to the Date table without removing the filter, then something like LASTNONBLANK('Date'[Date],[SalesAmount]) will give me 12/31 of the corresponding year for prior year's columns. And if I remove the Date filter via ALL() I will get the last available date, thus ignoring filters/slicers a user may have added.

I am fairly new to DAX, and was hoping that this was a common scenario that someone has already solved. But all I am finding are the solutions for YTD comparison with prior year, NOT YTD "trend" over multiple years.

Any feedback is really appreciated!

Vlad

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2018-06-13T17:18:54

You need to provide more details for me to be specific, but I can guess at your dataset.

Assuming:

table1 with Date, Period, Year and SalesAmount

AND

table2 with Period and Year

AND

No relationships.

Your measures might go like this:

MAX_PERIOD = CALCULATE(MAX(table2[Period]))

MAX_YEAR = CALCULATE(MAX(table2[Year]))

YTD Sales = Calculate(Sum([SalesAmount]), FILTER(table1, table1[Period] <= [MAX_PERIOD] && table2[Year] = [MAX_YEAR]))

And your Pivot Table/Matrix might look like this:

_____________________________
|Filters:    |Columns:      |
|            |table2[Period]|
|            |              |
|____________|______________|
|Rows:       |Values:       |
|table2[Year]|[YTD Sales]   |
_____________________________

DAX - YTD with year on columns

1 Answers