Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Qualube
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

@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
Ashish_Mathur
Super User
Super User

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/

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

 

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

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/

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?

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/

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.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.