Current YTD vs Previous Year YTD

DAX DAX Calculations
1

Hello,

I have a report that analyzes current vs previous year COGS. When the user opens the report and selects the current year, 2022, it shows the current year COGS of 330,000 USD (there were sales from Jan 1, 2022 until March 16, 2022), which is captured with the year slicer.

However, the previous year measure adds up ALL the COGS from 2021, rather than just looking at the sales from Jan 1, 2021 until March 16, 2021, to match the YTD period of 2022. \

One option is for the user to use the date slicers to select the period of Jan 1, 2022 - March 16, 2022, but I’m hoping there’s a way to write the dax so it automatically captures the dates on which there are transactions in the current year, and applies that date range to the total for the previous year.

That way it shows an accurate comparison of the first 3 1/2 month of 2022 with the first 3 1/2 months of 2021 without any slicer selections. The measures are simple sums of a COGS column, and I have a standard date table linking up to the transaction date.

Here’s a screenshot of what’s happening in my report when you select 2022. As you can see, The COGS YTD for 2022 is 330,000 while it is over 2M for the previous year. If we were looking at the same periods for current vs previous years, those numbers should me much closer together. The variance should be smaller, as should be the Variance %.

I think there is a way to use the MAX function as a filter nested within the TOTALYTD function, in order to grab the latest date on the transaction table, and use that latest date to apply a date range to the total for the previous year, but I haven’t gotten it to work.

Thanks for your help!

image
image1155×585 39.8 KB

1 Like
2

@pete.langlois Try this:

var _LastDate = MAX(FactTable[Date])
var _Result =
CALCULATE(
    [COGS Previous Year],
    DATESYTD('Date'[Date]),
       'Date'[Date] <= _LastDate
)
RETURN
_Result

OR

var _LastDate = MAX(FactTable[Date])
var _Result =
CALCULATE(
    [COGS],
    DATESYTD(
        SAMEPERIODLASTYEAR('Date'[Date])
    ),
       'Date'[Date] <= _LastDate
)
2 Likes
3

Hi @pete.langlois, did the response provided by @MudassirAli help you solve your query?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Thanks, @mudassirAli.

Here’s the DAX that finally worked for me. The -364 is because there are 364 days in a nonstandard 445 calendar, which is what my client is using.

COGS PFY =

VAR _MaxDate =
CALCULATE ( MAX ( transactions[Date] )-364, ALL ( transactions ) )

Return

CALCULATE([COGS], DATEADD(Dates[date], -364, day), Dates[Date] <= _MaxDate)

1 Like