cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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
Frequent Visitor
6 REPLIES 6
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 🙂

Super User

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
Frequent Visitor

Thanks @Ashish_Mathur.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft Employee

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

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.
Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.