YTD Sales-Same Period Last Year

[Mega79]

Hi all, 

I´m trying to compare my YTD data (that I made a meassure and it´s working fine) to the same period last year.

Basically I want to compare the january-may 2018 vs january-may 2017.

When I create the new meassure LY YTD (see meassure below), I just get the total sales from last year and not the january-may 2017.

Can anyone help me with this issue?

Thanks.

[Ashish_Mathur]

Hi,

I suggest you do this.

1. Create a Calendar Table with running dates
2. Extract the Month and Year in that Calendar Table with the following calculated column formulas = YEAR(Calendar[Date]) and FORMAT(Calendar[Date],"mmmm")
3. Create a relatiosnhip from the Date column of Table1 to the Date column of the Calendar Table
4. In your visual drag the Year and Month from the Calendar Table.  Select a certain Month.
5. Write these measures

Total Sales = CALCULATE(Data[Sales])

YTD Sales = CALCULATE([Total Sales],DATESYTD(Calendar[Date],"31/12")

LY YTD Sales = CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

Please change the column names in the measure.

Hope this helps.

[ChrisMendoza]

Hello @Mega79,

Do you mean for something like:

Where I've used the following:

Total = SUM(Table1[Value])

YTD Value = 
TOTALYTD(
    [Total],Table1[Date]
) 

Total LY YTD = 
CALCULATE(
    TOTALYTD([Total],Table1[Date]),
    SAMEPERIODLASTYEAR(Table1[Date])
) 

[Mega79]

I'm still not getting a sum of last year YTD data... I only get the total.

Somehow it's taking the entire year. I don't know if the formula is writen correctly.

[ChrisMendoza]

@Mega79, 

I guess you may need to show me the visual that you are trying to create as I do not understand.

My example could be visualized as:

How does your's need to be visualized?

[Mega79]

I need it to be visualized exactly as your exemple but when I do the meassure I don't get any data.

You can see the vusual below as a reference.

For last year YTD data I should get something close to the blue bar...

[ChrisMendoza]

@Mega79

What if you tried:

Ventas LY YTD = CALCULATE(Sheet1[Ventas YTD],SAMEPERIODLASTYEAR(Sheet1[Fecha].[Date])) 

Meaning remove the .[Date] from your Measure.

[Mega79]

I just erased the .[Date] from the meassure and still not getting any data.

[ChrisMendoza]

@Mega79,

I see Fecha is a function, what is the definition of Fecha?

The Data Type should be Date for Fecha.

[Mega79]

Fecha is only the date that I converted into the correct formart...

But I can also use the Recons_Date column which is the same as "Fecha" and I still don't get any data.

[Mega79]

I just used both the .[Date] and without the .[Date] and somehow the filter SAMEPERIODLASTYEAR is not doing the job of isolated the ytd of last year.

[ChrisMendoza]

@Mega79,

In my example, all the Dates are the first of the month formatted to 'MMMM YYYY'.

I recall that the Time Intelligence functions require a proper dCalendar table related to your fact table.

Possibly, with my small dataset, I've managed to over come that small detail; as seen by my demonstration.

At this point, it would not hurt for you to create a proper dCalendar table since you are unable to acheive the desired results.

Use this modified code from https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/ to quickly make a dCalendar that you can relate to your fact table (in Power Query Editor).

let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    StartDate = #date(2000, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate),
    Custom1 = #"Changed Type"
in
    Custom1

[Mega79]

I´ve created the calendar table as expleined in your link. Then I linked both the new calendar table with my sales table.

After that I created the meassure LY YTD but still getting the same result (the sum of last year) - see below

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.