Forum Discussion

Qualube's avatar
Qualube
Icon for Helper II rankHelper II
7 years ago
Solved

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...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Qualube,

     

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

Qualube's avatar
Qualube
Icon for Helper II rankHelper II
7 years ago

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's avatar
Qualube
Icon for Helper II rankHelper II
7 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      Qualube
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        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.