cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

Re: YTD last year DAX

@PavelR

I see...that could be bothersome having unwanted months showing up.

I would suggest that you have at least complete months in your Date table covering your Data date range, so you could redefine Date as:

Date = 
CALENDAR (
    EOMONTH ( MIN ( Data[Date] ), -1 ) + 1,
    EOMONTH ( MAX (Data[Date] ), 0 )
)

Given the way SAMEPERIODLASTYEAR, DATEADD and other time intelligence functions work, you will get funny results without complete months.

 

I updated my PBIX file above, and when 2017 is selected, a Month slicer will show only month 1.

 

On your question of why your intended behaviour isn't the default behaviour: Firstly, I agree that your intended behaviour is probably common to many businesses and I have seen it before. However, the time intelligence functions are intended to work with a Date table, without considering the exact dates that may be present in your fact table(s). It is possible that January has passed, but you had transactions on only 1-3 January. There is no way the DAX engine could know that January had concluded from the fact table alone, unless 'zero' rows were created for dates without transactions, or some other flag showed the last date.

 

Perhaps another approach would be to include full months in the Date table, but also include a binary column that indicates whether a date has passed or not, and that column could be referenced in your measures (rather than my approach which assumed the last date in the entire fact table is the latest date).

 

Cheers,

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

Thanks again @OwenAuger. Your comment with completed actual month in date table was really helpful for me.

 

Regards.

Pavel

Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

Hi @OwenAuger,

could you please help me with construction of formula to calculate Sales MTD LM (Last month)?

 

So I have standard measure for Sales MTD

Sales MTD = TOTALMTD(SUM(Data[Amount]);'Date'[Date])

And I would like to have measure to show me Sales MTD for last month - so when now is 17.1.2017, I have sales from 1.1.2017 to 17.1.2017. And then I would like to have Sales MTD LM from 1.12.2016 to 17.12.2016.

 

Is it possible, when the year in filter is set to 2017?

 

Thanks.

Pavel

Highlighted
Super User I
Super User I

Re: YTD last year DAX

Hi @PavelR

 

I would suggest a similar pattern to the YTD LY measure.

You can use DATEADD instead of SAMEPERIODLASTYEAR in this case.

INTERSECT(...) returns the selected date range limited by the latest date in the Data table, then DATEADD shifts it one month earlier.

 

Sales MTD Last Month (limited by last date in Data table) =
VAR DataMaxDate =
    CALCULATE ( MAX ( Data[Date] ); ALL ( Data ) )
RETURN
    CALCULATE (
        [Sales MTD];
        DATEADD (
            INTERSECT (
                VALUES ( 'Date'[Date] );
                DATESBETWEEN ( 'Date'[Date]; BLANK (); DataMaxDate )
            );
            -1;
            MONTH
        )
    )

Cheers,

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

Works perfect! Smiley Happy Thanks a lot @OwenAuger.

 

Regards.

Pavel

Highlighted
Anonymous
Not applicable

Re: YTD last year DAX

It is working well, but when I do filters like country or city, it still shows the same value and filters are not applying to this. Where I need to change if I want to apply filter.

 

Thanks,

Highlighted
Frequent Visitor

Re: YTD last year DAX

First of, thanks for all the help on the issue @OwenAuger however, I have a problem which I couldn't resolve at all since I am kind of beginner with DAX.

 

My dataset has unique Sales Year-Month-Day values from 01.01.2016 to 05.20.2017 in date format and was related with a Calendar Date column which ends at 05.20.2017 as well since it's the last data collection date.

 

First I was struggling to compare MoM for 2016 and 2017 sales. For example, May of 2017 contains only 20 days of data and I wanted to compare the same date range for May of 2016; however, it was fetching all the 2016 data till the end of the year. I solved that issue with your explanation.

 

For this year sales I used: 

 

This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESYTD('Calendar'[Date].[Date]))

 

And for last year sales:

Last Year Sales = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ); ALL ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        [This Year Sales];
			SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date].[Date] );
                DATESBETWEEN ( 'Calendar'[Date].[Date]; BLANK (); DataMaxDate )
            )
        )
    )

  

Since my intention was comparing apples to apples, this was the result I was looking for. I finally was able compare the first 20 days of May 2016 and first 20 days of May 2017. 

 

 

However, now I am not able to visually filter any of the months. I mean, when I click Last Year Sales, it's automatically accumulating This Year Sales as well. Please see below:

 

My intention is:

1. To be able to individually filter every year's sales like I was able to before.

2. If I cannot filter individually because of the formulas I used, I am kind of looking for a new way to compare last year's and this year's sales. Like I mentioned, last data date must be taken into account for last year's datas.

3. I created a different Calendar.[Date] and columns related with my sales date data. (Format is in date like 01.01.2016)

 

Thanks for all the help, rgrds,

Super User I
Super User I

Re: YTD last year DAX

@Blackworms

When you have a clustered column chart with two or more measures as Value fields, visual filters resulting from clicking on the bars are applied to the axis field.

 

In your example, the visual filter is applied to the month dimension on the axis to include only "May", but both measures are still visible.

 

You can de-emphasise bars for a particular measure by clicking on the legend, but this can't be done simultaneously with an axis dimension filter. Also, I don't think this 'filters' any other visual in any way.

 

Also I couldn't work out what the $3.68m represents. Are you saying that's the total of Last Year Sales and This Year Sales?

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Helper III
Helper III

Re: YTD last year DAX

@OwenAuger

 

Thanks. This helped me a lot.

Highlighted
Responsive Resident
Responsive Resident

Re: YTD last year DAX

@OwenAuger: I tried your DAX formula and it works, to an extent. It works for the current year. I'm looking to display a table that shows the following three columns: [Year], [YTD], [YTD Last Year].

 

To test your formula I built a simple Excel sheet with two columns: [Sale Date] and [Sales] and populted it with every date between 1/1/2015 and today (8/18/2017) and $1.00 of Sales on each day (hey, makes it easy to test, right?). This table is joined to a fully populated Date table with dates ranging back and forward several years.

 

After adjusting your formula and building my table, I see this:

2017-08-18_10-35-11.jpg

 

2017 is showing correct LYTD value of 231 (one over this year because of Feb. 29 2016) but Year 2016 should show 230. Instead it show 365.

 

Seems LYTD should be a VERY simple calculation in DAX. (MDX had the function ParallelPeriod that made this SUPER EASY!) I've spent hours pouring over blogs, forums and technet and still can't seem to get it.

 

Frustrated.

 

Thanks in advance.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors