cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Re: YTD last year DAX

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

Highlighted
Helper V
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
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
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
https://www.linkedin.com/in/excelenthusiasts/
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
Helper V

Re: YTD last year DAX

Hi @Robert_Jensen,

 

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]))
 
Your help is much appreciated!
 
Regards,
Robert
Highlighted
Helper V
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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors