Need help creating YTD and LYTD and make Index as Card

[PeterStuhr]

Hi,

I am having problems making my LYTD work.

I have a SalesTable containing "Order Date" and "Revenue EUR".

I have created a Year To Date on "Revenue EUR" by the quick measure feature. I selected the "Order Date" as date.

This seem to work.

My problem

When creating "Last Year To Date", I have tried the following:

LYTD Sales = CALCULATE([SumOfSales]; DATEADD(SalesTable'[Order Start Date.Date];-1;YEAR))

Also tried:

LYTD Sales2 = CALCULATE([SumOfSales2];SAMEPERIODLASTYEAR('Sales (2)'[Order Start Date.date]))

Today is 1 May 2019. So my goal would be, that it sums 1 Jan 2018 - 1 May 2018.

What both of them does is that it sums 1 Jan 2018 to May 31 2018.

Why is that?

[Anonymous]

@PeterStuhr 
Do you have a dedicated Calendar table?  If so, is it marked as data table?

That's the first ( and really most important) step to ensure all the time-intelligence functions work correctly.

-N

[PeterStuhr]

Hi, thanks!

I have just created a table containing Dates and set it as Date table.

However, it does not seem to fix my issue.

[Anonymous · Accepted solution]

Need to be sure that your date table contains all dates, not just dates you have in your Sales Table.  Few ways to ensure that, but can create a calculated table with the following code ( just be sure to swap out the Orders [Order Date])

DimCalendar =
ADDCOLUMNS(
    CALENDAR(
        DATE( YEAR(MIN( Orders[Order Date])),1,1),
        DATE( YEAR( MAX ( Orders[Order Date])),12,31)),
        "Year", YEAR([Date]),
        "Month", MONTH( [Date] ),
        "MonthName", FORMAT( [Date], "mmmm"),
   )

Relate that your fact table and use the columns from DimCalendar for filters ( rows, columns, slicers, etc)

Then using the following measures, you can get YTD and LYTD sales. 

Total Sales = 
    SUM( 'FactOrder'[TotalSales] )

YTD Sales = 
    CALCULATE( 
        [Total Sales], 
        DATESYTD(DimCalendar[Date])
    )

LYTD Sales = 
    CALCULATE( 
        [YTD Sales], 
        SAMEPERIODLASTYEAR( DimCalendar[Date] )
    )

 which produces this table 

[PeterStuhr]

Thanks! I can see that it does what I want it to in the table.

However, what I really need is a YTD Index. I created it like this "Index test = ([YTD Sales]/[LYTD Sales])*100" and put it in the table.

As you can see we are index 94 right now (May 1). That number, I would like to show always in a card. But when I put it in the card, it does not understand it. Is there a way I  can display this number in a card, without having the table?

[Anonymous]

No problem, would just have to add in a few lines of code to account for the date. I'm not sure on your data set and how often it refreshes, but do you want it show the last avialable date or do you want it based on today's date. So if there is no sales data for today, the card could say something like "No Data Availble" or something like that. 

[Mariusz]

Hi @PeterStuhr 

The below should do the trick, just need to adjust it slightly.

LYTD Sales = 
TOTALYTD(
    CALCULATE(
        [Sales], --Your Measure
        SAMEPERIODLASTYEAR('Calendar'[Date]) --date from your Date dimension
    ),
    'Calendar'[Date], --date from your Date dimension
    ALL('Calendar'[Date]) -- Date dimension table
)

Hope this helps 

Mariusz

[PeterStuhr]

Hi,

I have made the following, but it is blank:

LYTD Sales =

TOTALYTD(

CALCULATE(

[SumOfSales]; --Your Measure

SAMEPERIODLASTYEAR('date'[Date]) --date from your Date dimension

);

'date'[Date]; --date from your Date dimension

ALL('date'[Date]) -- Date dimension table

)