Assistance on relative dates (fiscal years)

[dave_dingle]

Hi, I have a number of dates within my dataset (across different tables) that capture details such as:

• File opened date
• File closed date
• Cancellation date
• Time written off date

My requirement is to create two cards that simply display for example, the number of files opened this month and for the fiscal year (April to March).  This months closed files is simple as I'm able to use the 'File opened date' (relative date = in this month).  The problem I have is for the second one which is for the fiscal year.  If I use the inbuilt 'File opened date' (relative date = in this year the) results are incorrect as it assumes my year is from Jan-Dec.

I do have an existing calendar table which does contain offeset columns for fiscal year, quarter and month etc. but i'm not sure if/how I can use these as a filters or perhaps I need to create new columns for each of the dates that displays whether its in the current fiscal year or not, then I could use a filter for this new field  i.e. File opened this year flag = Yes.  If this is the best route I'd just need to understand the DAX

Thanks in advance for any assistance, hope this makes sense.

[parry2k]

@dave_dingle you can take advantage of the calculation group. I recently wrote a blog post on this, check it here The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com)

For Fiscal YTD, you can simply use the function TOTALYTD and pass the parameter to tell the fiscal year-end and in your case, it will be "03/31" or DATESYTD, here is a link to both the functions DATESYTD function (DAX) - DAX | Microsoft Docs TOTALYTD function (DAX) - DAX | Microsoft Docs

and similarly, you have TOTALMTD and DATESMTD functions to calculate Month total.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[vanessafvg]

can you share what data model your currently have?   

If you have all the dates and the required calendar and fiscal year date columns your calendar table it should be fine.  Would be useful to see your relationship view and some samples  of the data.  

To use multiple dates against a date table you need to set up one active relationship between that date and the date table, then the rest are set up as inactive relationships and then you need activate them in the code for each the measures you  need should you need to use one of the inactive dates.  You can only have one active relationship between tables.  If all those dates are sitting in one  table.  You will need to use userelationship() function with your measures to activate the inactive relationship.

[dave_dingle]

Many thanks for your message.

So below is an example of my dataset relationships.  You can see I have a calendar table which is connected to the matter table (this just holds legal case files).  I believe the relationship is for calendar (date) --> matter (opened_date) but I'd also like to run reports on things such as the highlighted date fields.  I also have another table which has a similar requirement i.e. multiple dates within it that I wish to capture 'this fiscal year' info on as well so all dates aren;t within the same table.

Hope this clarifies things.