YTD

[Qualube]

Hi

I am trying to get the YTD totals from the previous year and cannot see to get it to work. I have read extensively online and my Date table has no gaps and was created using CALENDARAUTO.

My problem is that I have created the measure below that basically calculates the volumes up until the last delivery date with the fiscal year set to 31.3.

YTD VOLUME = CALCULATE([VOLUME], DATESYTD('DATE'[Date],"31/3"),
    FILTER(SALESLINE,SALESLINE[DEPOT] = "UK"),
    FILTER(SALESLINE,SALESLINE[DELIVERY DATE] <= LASTDATE('DATE'[Date])))

Then I have called SAMEPERIODLASTYEAR to get the corresponding volumes hoewever I get the entire 12 months and not April to Last delivery date as expected.

SAME LY = CALCULATE([YTD VOLUME], SAMEPERIODLASTYEAR('DATE'[Date]))

I have tried numerous other funtions like PARRELLPERIOD but I am convinced SAMEPERIODLASTYEAR is the correct one and should work.

Thanks

[Vander1981]

Hi,

Try this :

YTD VOLUME =
CALCULATE ( [VOLUME], DATESYTD ( 'DATE'[Date], "31/3" ) )

LYTD VOLUME =
VAR LastDateInFact =
    CALCULATE ( LASTDATE ( FACT[Date] ) , ALL(FACT) )    which is the last date available in your fact table.
RETURN
    TOTALYTD (
        [VOLUME],
        SAMEPERIODLASTYEAR (
            FILTER ( VALUES ( 'DATE'[Date] ), 'DATE'[Date] <= LastDateInFact )
        ),
        "31/3"
    )
   

[Qualube]

Thank you very much for the reply, the variable works however it still returns the fulkl 12 months and I only want April to Today as a comparison.

[Vander1981]

That's because in your fact table you have data up to march 19 right ?

If it is the case you should adapt the variable to display only the last date of sales.

[Qualube]

Thanks I thoght that so I changed the LASTDATE to return the last delivery date but I still get the full 12 months!!

I can see that the variable is now storing the last delivery date and FILTER is returning the full DATE table and comparing it to the variable but can't figure why the full year is coming back.

LYTD VOL =
VAR LastDateInFact =
    CALCULATE ( LASTDATE (SALESLINE[DELIVERY DATE] ) , ALL('DATE') )
RETURN
        TOTALYTD([VOLUME],
        SAMEPERIODLASTYEAR(
            FILTER ( VALUES ( 'DATE'[Date] ), 'DATE'[Date] <= LastDateInFact )
        ),
        "31/3"
    )

[v-jiascu-msft]

Hi @Qualube,

It should work. Can you share a sample? Please mask the sensitive parts. 

The logic is clear. So it could be the issue of the data structure.

The [VOLUME] seems a measure. What's its formula?

Best Regards,
Dale

[Qualube]

Hi Dale

Absolutely I agree the logic seems sound, hence my frustration. The Volume is a measure listed below which basically sums up the volume 

VOLUME =
SUMX(SALESLINE,SALESLINE[MULTIPLE PACK] * RELATED('Pack Size'[Quantity]))

Think I may have solved it by using but not sure if this is correct or not really I can't see why the orginal logic doesn't work and this does!!!:

SAME LY = CALCULATE ([VOLUME],
    FILTER(SALESLINE,SALESLINE[DATAAREAID] = "UK"),
    DATESBETWEEN (
        SALESLINE[DELIVERY DATE],
        DATE ( 2017, 4, 1 ),
        EDATE ( TODAY (), -12 )))

[v-jiascu-msft]

Hi @Qualube,

That's great. The YTD is evaluating the accumulative total from the start of the year. If you always get a total of 12 months, it could be the wrong context. Please check out the demo in the attachment. 

BTW, the Time Intelligence functions like DATESBETWEEN require a date table that has continuous dates. 

Best Regards,
Dale

[Qualube]

Hi Dale

Thanks for the reply, the more I look at this the more I suspect it may be my date table which I created using:

DATE = ADDCOLUMNS(
    FILTER(
    CALENDARAUTO(3),
    YEAR( [Date]) >= YEAR (MIN(SALESLINE[DELIVERY DATE]))
),
"Year", YEAR( [Date]),
"Month", FORMAT([Date], "mmm"),
"Month Number", MONTH ([Date])
)

But on checking this has created a continous date range from 1st January 2011 up until 31st March 2019 which covers the data model.

[v-jiascu-msft]

Hi @Qualube,

Can you share your file or a sample?

Best Regards,
Dale