Michael has attended:
Power BI Modelling, Visualisation and Publishing course
Annualised Financial Year
I have data for hundreds for non-conformances, I will make a reference table to create financial years.
What would be the best way to create an annualised DAX formula that didn't need manual intervention each year?
RE: Annualised Financial Year
Hi Michael,
Thankyou for your question to the forum
In order to help solve your problem, I will need some more detail, ie. a sample of your data. You can block out any sensitive info. Please send it to:
info@stl-training.co.uk
Also, I assume when you mention a reference table to create financial years, you mean a date table. If you could send this as well, that would be great
Kind regards
Martin
RE: Annualised Financial Year
Hi,
So I have three tables:
1) A Financial Year Table FY[] that has a column a month year "MMM YYYY" and one for the corresponding Financial Year
Jan 2024 | FY24
2) I have a DateDimension Table that contains every date until 2044, it also relates the month year column from Financial Year table, so that it links the FY to a specific date
3) I have a raw data table that contains all quality defects raised by a customer, this table contains the date the defect was raised in the system and the type of defect.
My goal is to have a pareto of the top defect types by financial year (including annualised for current Financial year). The struggle is that when we are in FY24, i would like to have it annualised, so that I can see where we are headed improving or deteriorating.
Do you have any ideas for a measure and calculation that could link these tables?
Thanks
Michael
RE: Annualised Financial Year
Hi Michael,
Thanks for your follow up information explaining your specific issue around annualising main defects for a financial year.
It sounds like want you need is very complex so I'm not able to give you a finished solution. However, what I can suggest is a way to annualise in a dynamic way the count of defects based on how far into the financial year the defect occurred:
DIVIDE(COUNT([KMS Main Defects])*365.25,
DATEDIFF(DATE(YEAR(TODAY()),07,01),
TODAY(),DAY))
I hope this measure goes some way to arriving at a solution
Kind regards
Martin
(IT Trainer)
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
