98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Variance
Variance
Resolved · Medium Priority · Version 2016
Mon 9 Mar 2020, 14:34Delegate Nicola said...
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?
Mon 9 Mar 2020, 15:25Trainer Jens said...
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
Wed 11 Mar 2020, 09:42Delegate Nicola said...
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
Wed 11 Mar 2020, 11:49Trainer Jens said...
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
|
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Create Interactive DashboardsBuild 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. |
