Forum Discussion

lnjwilliamson86's avatar
lnjwilliamson86
Frequent Visitor
8 years ago
Solved

Calculating % Variance between YTD & LYTD

Hi,    I am trying to calculate the variance % between a YTD and LYTD. However my formulas are giving me incorrect results.  I have monthly data over two financial years for various metrics in my ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You will have to select a month for the TOTALYTD() function to work.  Only then will it know where it has to sum till.  If your Financial Year starts on July 1 and you select October as the month in the filter/slicer, your measure will sum up the numeric column from JUly 1 to October 31.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

lnjwilliamson86,

Do you drag year field to your visual? And when calculating LYTD, I use the following DAX. I note that you use this formula instead:LYTD =CALCULATE([metric],DATEADD('date'[date],-1,year)). Do you create another measure named metric?

LYTD =CALCULATE([YTD],DATEADD('date'[date],-1,year))



Regards,
Lydia

lnjwilliamson86's avatar
lnjwilliamson86
Frequent Visitor
8 years ago

Hi Anonymous,

 

My LYTD Calcualtion does use the YTD Metric (see actual formulae below) . However when I put my data into a table format I can see that the LYTD is giving me the sum of the full year not just the first quarters data.

 

 

See actual formulae below.

 

6. YTD Water Consumption Billed = TOTALYTD(sum(Datasheet[6. Total water consumption billed (based on Levy data)]),'Date'[Date],"06/30")

 

6. LYTD Total Water Consumption Billed = CALCULATE(Datasheet[6. YTD Water Consumption Billed],DATEADD('Date'[Date],-1,year))

 

Water Consumption Variance = [6. YTD Water Consumption Billed]-[6. LYTD Total Water Consumption Billed]

 

Water Consumption Variance % = DIVIDE([Water Consumption Variance],[6. LYTD Total Water Consumption Billed])