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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
lnjwilliamson86
Frequent Visitor

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 dataset

 

The forumulas I have used are listed below. 

 

YTD= TOTALYTD(SUM[metric],'date'[date],"06/30")

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

Variance = [LYTD]-[YTD]

% Variance = DIVIDE([Variance],[LYTD]

 

Is there some sort of date field required in the variance and % variance formulas? 

 

Thanks

 

 

2 ACCEPTED SOLUTIONS

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.


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

View solution in original post

6 REPLIES 6
lnjwilliamson86
Frequent Visitor

After some further investigating (aka messing around) I have realised that if i select the three months in this FY to date in a visual level filter the variance and % variance results work. 

 

Can someone help me with some DAX code to ensure that my variance and % variance measures will work so that I don't need to use the month visual level filter. 

 

 

Thanks 🙂 

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.


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

Thanks @Ashish_Mathur.

You are welcome.


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

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

1.JPG

Regards,
Lydia

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

Hi @v-yuezhe-msft,

 

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.

 

Capture2.PNG

 

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])

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!