Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a number of dates within my dataset (across different tables) that capture details such as:
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |