Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.
Hi,
Use the SAMEPERIODLASTYEAR() function. To get more specific help, share your data and show the expected result.
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.
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.
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.
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
85 | |
67 | |
49 |
User | Count |
---|---|
132 | |
113 | |
100 | |
68 | |
67 |