cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Running Comparion to previous year

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Running Comparion to previous year

@Qualube,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Running Comparion to previous year

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Running Comparion to previous year

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

 

Highlighted
Helper II
Helper II

Re: Running Comparion to previous year

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

Highlighted
Super User IV
Super User IV

Re: Running Comparion to previous year

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Running Comparion to previous year

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?

Highlighted
Super User IV
Super User IV

Re: Running Comparion to previous year

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Running Comparion to previous year

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.

Highlighted
Community Support
Community Support

Re: Running Comparion to previous year

@Qualube,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors