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
