YTD (this year) vs YTD (last year)

[arniet]

Hello, I need advice on why my YTD (this year) is showing data for 1 Jul 2020 to 2 Jul 2020, while my YTD (last year) is showing data for all of Jul 2019? Shouldn't it be showing data for 1 Jul 2019 to 2 Jul 2020? 

kind regards

arnie

[arniet · Accepted solution]

Hi Everyone,

I have worked out the DAX that resolves all MTD LY, QTD LY and YTD LY. Added bonus was creating the 4th LY measure that was based just on any actual date of the year. Here goes:

MTD (Prev.) Net Sales ($) = CALCULATE(TOTALMTD([Net Sales Exc. Tax],

'Invoice Date Dimension'[Invoice Date]),
FILTER(DATESQTD(SAMEPERIODLASTYEAR('Invoice Date Dimension'[Invoice Date])),
MONTH('Invoice Date Dimension'[Invoice Date])<MONTH(TODAY()) ||
(MONTH('Invoice Date Dimension'[Invoice Date]) = MONTH(TODAY()) &&
DAY('Invoice Date Dimension'[Invoice Date]) <= DAY(TODAY()))))

QTD (Prev.) Net Sales ($) = CALCULATE(TOTALQTD([Net Sales Exc. Tax],

'Invoice Date Dimension'[Invoice Date]),
FILTER(DATESQTD(SAMEPERIODLASTYEAR('Invoice Date Dimension'[Invoice Date])),
MONTH('Invoice Date Dimension'[Invoice Date])<MONTH(TODAY()) ||
(MONTH('Invoice Date Dimension'[Invoice Date]) = MONTH(TODAY()) &&
DAY('Invoice Date Dimension'[Invoice Date]) <= DAY(TODAY()))))

YTD (Prev.) Net Sales ($) = CALCULATE(TOTALYTD([Net Sales Exc. Tax],

'Invoice Date Dimension'[Invoice Date],"30/6"),
FILTER(DATESYTD(SAMEPERIODLASTYEAR('Invoice Date Dimension'[Invoice Date])),
MONTH('Invoice Date Dimension'[Invoice Date])<MONTH(TODAY()) ||
(MONTH('Invoice Date Dimension'[Invoice Date]) = MONTH(TODAY()) &&
DAY('Invoice Date Dimension'[Invoice Date]) <= DAY(TODAY()))))

Net Sales Exc. Tax Same Time Last Year =

CALCULATE(SUM('Current Order Detail Fact'[Net Sales Amount Excl Tax]),
FILTER(SAMEPERIODLASTYEAR('Invoice Date Dimension'[Invoice Date]),
MONTH('Invoice Date Dimension'[Invoice Date])<MONTH(TODAY()) ||
(MONTH('Invoice Date Dimension'[Invoice Date]) = MONTH(TODAY()) &&

DAY('Invoice Date Dimension'[Invoice Date]) <= DAY(TODAY()))))

[stevedep]

YTD, should show from Jan first to today.

[arniet]

Hi @stevedep 

My apologies for not being clear, my YTD calculations are based on the financial year, starting Jul.

kind regards

arnie

[AlexAlberga727]

Create a date table identifying your fiscal years. Will assist you greatly in the long run. Then you're able to create measures based off of your fiscal qtrs and years opposed to jan-dec. Just a tip.

[arniet]

Hi @AlexAlberga727 , thanks for the advice. Yes, I am currently using a date table that identifies my fiscal years. Still getting stuck with the DAX for some reason.

[stevedep]

Can you share your DAX codes so we can help?

[arniet]