Running Comparion to previous year

[Qualube]

Hi

I need to establish the comparative volumes from the previous year against the current year. I think I am part way there but can't finalise the measure to achieve this.

I know I need to filter the sales table to identfy the last delivery date

FILTER(SALESLINE,SALESLINE[DELIVERY DATE] >= MAX(SALESLINE[DELIVERY DATE]))

This then gives me the volumes up to the last delivery date in the current year but I can't seem to pin down the dax funtion to do the comparison to the exact same time last year  whether it's PARRELLPERIOD or DATESBETWEEN

[Ashish_Mathur]

Hi,

Use the SAMEPERIODLASTYEAR() function.  To get more specific help, share your data and show the expected result.

[Qualube]

Hi Ashish

I am pretty new to DAX but I am really enjoying the learning process. I have broken this problem down into smaller less complex steps and I have the first measure written which basically SUMX the volume of product sold from the 1st April until the last delivery date. This gives me a running total of volumes sold but what I would like to do is take this running total and see the exact same timeframe last year as a comparison.

I have tried to use the measure below in a SAMEPERIODLASTYEAR function but cannot get the syntax right.

RT VOLUME = CALCULATE(SUMX(SALESLINE,SALESLINE[NEW VOLUME]), DATESBETWEEN('DATE'[Date], 
    DATE(2018,4,1), 
    MAX(SALESLINE[DELIVERY DATE]) 
  ))

What I would like to do is take

[Qualube]

Just a quick update, tried:

PY RUNNING TOTAL =
CALCULATE (
    [RT VOLUME],
    SAMEPERIODLASTYEAR ('DATE'[Date])
)

But it gives me exactly the same figures as [RT VOLUME] so can't seem to make sense of that!!!

Thanks

[Ashish_Mathur]

Hi,

The SUMX is not required.  Try these measures:

RT_Volume = SUM(SALESLINE[NEW VOLUME])

PY RUNNING TOTAL = CALCULATE([RT VOLUME],SAMEPERIODLASTYEAR('DATE'[Date]))

There should be a relationship from the Date column of the Salesline Table to the Date column of the Date Table.

Hope this helps.

[Qualube]

Hi Ashish

Thanks for the reply but just a question before I go ahead.

If I use RT_Volume = SUM(SALESLINE[NEW VOLUME]) will that not add all the volumes and not just the ones for this financial year?

Don't I need this filter so I don't return all volumes since the very first delivery date?

[Ashish_Mathur]

Hi,

Create 2 slicers for Year and Month (both from the Date Table) and select a certain year/month.  Try this measure

RT_Volume = CALCULATE(SUM(SALESLINE[NEW VOLUME]),DATESYTD(Date[Date],"31/3")

The other measure will remain the same.

[Qualube]

Hi Ashish

Finally solved it I think it was to do with my initial evaluation context anyway I have addressed that and created the following  measure

PY VOLUMES = IF(LASTDATE('DATE'[Date]) > TODAY(), BLANK(),
    CALCULATE( [YTD VOLUMES], DATEADD('DATE'[Date],-1,YEAR)))

Which has given me  a running total that updates automatically.

Thanks for your help.

[v-chuncz-msft · Accepted solution]

@Qualube,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.