variance

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Variance

Variance

resolvedResolved · Medium Priority · Version 2016

Nicola has attended:
Power BI Reporting course

Variance

I have a raw data file for customer orders and need to look at the variance vs LY by week, month and year to date which I would need to update on a weekly basis. What is the easiest way to do this?

RE: Variance


Hi Nicola,

Thank you for the forum question.

You will need to have a date table in your data model and you will need to have the week numbers in the date table.

The functions Sameperiodlastyear, ToTalYTD, Calculate, and Sum or Sumx can do the job.

I do not know the names of your tables or your field names.

The structure is Total Sale=Sum("name of table"["Column name"])

The measures below (you will need to change the names and use your names) can hopefully help you in the right direction.

Total Sales = SUM(Sales[SALE])

Total Sales YTD = TOTALYTD(Sales[Total Sales],'Sales'[DATE])

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('date'[date]))



SAMEPERIODLASTYEAR('date'[date]) the 'date'[date] is the primary key from the date table.

We have all these functions on our DAX course and much more.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Variance

Thanks Jens!

I have created a date table but now I need to add a measure to calculate financial year. Can you please help we with the DAX formula I would need to do this. Please see below dates by financial year.

FY19 – May 2018 – Apr 2019

FY20 – May 2019 – Apr 2020

FY21 – May 2020 – Apr 2021

FY22 – May 2021 – Apr 2022

Thanks
Nicola

RE: Variance

Hi Nicola,

Have you created the date table in Excel? Or have you created the date table by DAX?

If you have created it in Excel you can add more columns to your date table you can use for your Financial Years, Financial Months, and Financial Quarters.

I have a Date table created in Excel with the Financial periods.

If you want my date table you can send an email to info@stl-training.co.uk, then I will send you my date table.

It is a public forum and I don't want you to write your email here.

If you have a DAX date table, it is more complicated and you will need to have some advanced knowledge of DAX.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Create Interactive Dashboards

Build interactive dashboards by leveraging features like slicers, drill-through, and bookmarks. Slicers allow users to filter data dynamically, while drill-through enables detailed exploration of specific data points. Bookmarks help you save the current view, making it easy to switch between different states of your report.

View all Power BI hints and tips


Server loaded in 0.11 secs.