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.
Hello,
I have issues with figuring out how to calculate average of values for a fiscal year based on two date columns that are in another table.
There are 3 tables total:
1. Department Name, Date, Start Date, End Date.
Columns Start and End date are a range of a fiscal year. Each department has a different start and end dates of a fiscal year.
There is one row for every month available for each department.
Table 1 filters table 2, many to many relationship from Department to Department Name.
2. Department Name, Date, Average of Values.
There is one row for every month available for each department.
3. Calendar table which filters table no. 2.
I wrote a following calculation but it obviously doesn't work with fiscal year dates being in a different table:
@Anonymous , I think this can help
Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA
Hi, thanks for that. Unfortunately I was not able to find a working way of connecting those two tables together. I understand it is a matter of creating a correct inactive relationship between dates columns. I tried including userelationships in formula but to no avail. I am not able to find a proper way to have this calculation include a part that would allow for it to get filtered by my calendar.
I attach a screenshot of a model. I tried playing with inactive relationships beween calendar date and FYFrom, FYTo, MM-YYYY
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |