cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Re: YTD last year DAX

do the table calendar have to be related with the data table?

Highlighted
Helper V

## Re: YTD last year DAX

Yes the date table needs to be related to data table - you can see that in the sample file provided in solution.

Highlighted
Helper IV

## Re: YTD last year DAX

I used the following formula to find YTD and YTD LY

YTD = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY()),1,1) && [Date]
<=(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)))

YTD LY = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY())-1,1,1) && [Date]
<=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1)))

when I select year 2018 It only showes me YTD, how can I fix this to look both YTD and YTD LY when year 2018 is selected?

Highlighted
Super User IV

## Re: YTD last year DAX

Hi,

What exactly are you trying to do?  Describe your question in detail and share some data.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Frequent Visitor

## Re: YTD last year DAX

thank you so much

Highlighted
Frequent Visitor

## Re: YTD last year DAX

Hi Owen,

Is it possible to get the logic below with the exception that it calcultates sales amount with a start date based on selected value from a date slicer?

```Sales Amount YTD based on current date =
VAR Today =
TODAY ()
VAR TodayMonth =
MONTH ( Today )
VAR TodayDay =
DAY ( Today )
VAR MaxDateSelectedYear =
YEAR ( MAX ( 'Date'[Date] ) )
VAR TodayShiftedToSelectedYear =
DATE ( MaxDateSelectedYear, TodayMonth, TodayDay )
RETURN
CALCULATE (
[Sales Amount],
DATESYTD ( 'Date'[Date] ),
CALCULATETABLE (
DATESYTD ( 'Date'[Date] ),
'Date'[Date] = TodayShiftedToSelectedYear
)
)```

At the moment it always return YTD from 01-Jan to max TODAY or to a selected date less than TODAY. But when selecting the date range with start 10-Jan-19 to 17-Jan-19 it still returns the sales amount for 01-Jan-19 to 17-Jan-19.

Regards,

Robert

Highlighted
Helper V

## Re: YTD last year DAX

That mean you are not after really YTD rather data between selected dates. If you select full year, you want actually YTD data and if you select some mid range of dates, you want data for those dates only, hence aggrigation for selected dates and not YTD.

In that case, don't use DATESYTD because this will always start from first day of selected year. May be something like below:

```Sales Amount YTD based on current date =
CALCULATE (
[Sales Amount],
FILTER ( 'Date', 'Date'[Date] >= MIN ( 'Date'[Date] ) ),
FILTER ( 'Date', 'Date'[Date] <= 'Date'[Date] )
)```

emudria

Highlighted
Frequent Visitor

## Re: YTD last year DAX

Thank you very much @emudria!

That worked exactly as I wanted it to. How would you create a LY-measure to behave accordingly for the same period last year? Assume it's a slight change in the definition of date filtering.

I tried this formula but it doesn't set a last date to maximum TODAY -1 year.

Sales Amount LY  = CALCULATE([Sales Amount YTD based on current date];SAMEPERIODLASTYEAR('Date'[Date]))

Regards,
Robert
Highlighted
Helper V

## Re: YTD last year DAX

What are you getting from

`Sales Amount LY  = CALCULATE([Sales Amount YTD based on current date];SAMEPERIODLASTYEAR('Date'[Date]))`
Highlighted
Frequent Visitor

## Re: YTD last year DAX

It works fine as long as I don't extend my date filter to more than TODAY.

If I for example set the date filter to 1-jan-19 to 31-dec-19 the Sales Amount LY measure will sum upp sales for 1-jan-18 to 31-dec-18. The behaviour I'm looking for is that it in this case only calculate for 1-jan-18 to 13-feb-19 (today - 1 year as max).

Thought that if I use a YTD measure the way you fomulated it, I would do exactly that.. 🙂

So my problem is really that I would like a LY-measure that use MIN selected day -1 year and then as TODAY -1 year as MAX selected value.

Regards,

Robert

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.