I created a time dimension to follow our fiscal years. A bunch of my dashboards contain equations that need the number of days that are in the fiscal year for the formula.
Is there any way to count the number of days contained in each fiscal year from my time dimension?
Hi @patrick,
Try something like this in DundasScript.
var parsedDate = DateTime.Parse($Date$);
int days = DateTime.DaysInMonth(parsedDate.Year, parsedDate.Month);
return days;
here’s my setup:
Thanks @jeff, I gave it a try but since I am trying to capture the number of days in a year, it didn’t return anything. I am still playing around with it. Any other thoughts that might direct me in my research?
Hi @patrick,
Happy to take a look, but after reading your last comment I am no longer clear on what you want to do. Can you provide an example?
Thanks!
No problem @jeff, I hope this provides a bit more clarity.
I created this fiscal calendar in the time dimensions
Here is the data I am trying to capture.
Now that I have the time dimension created I am trying to capture the number of days in each fiscal year as illustrated in the days column above.
Does this make a bit more sense?
Hi @patrick,
I don’t think that you’re going to be able to do this with the time dimension alone. If you had the FY Start and FY End in your data, you could use a formula to calculate the days. But, in your case, I don’t think enough information is available to calculate what you want.
You could get down to the day level if you provide day in the dimension, but the results will be dependant on your data what will show up in the resultant dataset. From below, Jun 01, 2012, might just be the first data point that matches in that year, not the actual fiscal start.
Thanks for looking into this Jeff, I ended up building a solution in SQL to get around this.
