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 all,
Having an issue with filtering a chart that contains a measure which subtracts a 3-month average of Actuals pre Project start date (Dec19) from current Actuals.
Per screenshot below, when I add a Date filter with advanced filter 'is on or after' Dec19, I still see all of 2019 and 2021.
I only want to see Dec19 to today's date (Nov20).
VALUE MEASURE
SUPPORTING MEASURES
Solved! Go to Solution.
Hi @Anonymous ,
Sorry for the late reply,try to use "dateadd" instead of "dateinperiod":
AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATEADD( 'CALENDAR'[Date] , -3 , MONTH )&&
MAX( PROJECT_LOOKUP[RFS.DATE] )<=DATE(2020,11,31)))
/3
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @v-kelly-msft , understood re. the relationships, however even with the correct schema, the visual still shows all of 2019 and 2021 (albeit now with blanks).
I want to only show the months of 2020 up until November.
Thanks,
Jake
Hi @Anonymous ,
How about modifying your measure as below:
AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATESINPERIOD( 'CALENDAR'[Date] , EDATE( MIN( PROJECT_LOOKUP[RFS.DATE] ), -1 ) , -3 , MONTH )&&
MAX( PROJECT_LOOKUP[RFS.DATE] )<=DATE(2020,11,31)))
/3
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @v-kelly-msft ,
Unfortunately this modification generates an error:
"A function 'DATESINPERIOD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
I tried adding a 'FILTER ( ALL (' to correct it but still came up with the same error.
I do think that the answer is somewhere in modifying one or more of these measures though
Any thoughts?
Kind regards,
Jake
Hi @Anonymous ,
Sorry for the late reply,try to use "dateadd" instead of "dateinperiod":
AVG.PRE-REV =
CALCULATE(
[MRR(no RFS)]
, DATEADD( 'CALENDAR'[Date] , -3 , MONTH )&&
MAX( PROJECT_LOOKUP[RFS.DATE] )<=DATE(2020,11,31)))
/3
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @v-kelly-msft,
That measure still generates an error:
"A function 'DATESADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Jake
@v-kelly-msft thanks for the response.
I still have the issue that the months are still visible outside of the range I want to see (i.e. all of 2019 and 2021).
Also, in my actual data model I have other tables linked to the 'CALENDAR' table and 'PROJECT_LOOKUP' tables so that I receive this error when I try to change that relationship to 'Both':
Hi @Anonymous ,
Direction is important in relationships,you may try star schema instead.
See the reference below:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |