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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors