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 use a calendar but with date = first date of each month (12 rows for each year) in relationship (Year-Month) with a data base like this:
Salesman Sales YM Date Year MonthName
A 10 201901 01/01/2019 2019 January
B 15 201902 01/02/2019 2019 February
C 5 201801 01/01/2018 2018 January
C 5 201802 01/02/2018 2018 February
and i have an expression which is no more working (when the calendar was day by day full year it was ok but i need in this form to reduce optimize the report)
Thanks,
Cosmin
Solved! Go to Solution.
Hi @cosminc ,
Please modify the formula as below:
YOY = VAR TY_sales = SUM ( Source[Sales] ) VAR LY_sales = CALCULATE ( SUM ( Source[Sales] ), FILTER ( ALLSELECTED ( Source ), Source[Year] = SELECTEDVALUE ( Source[Year] ) - 1 && Source[MonthName] = SELECTEDVALUE ( Source[MonthName] ) ) ) RETURN DIVIDE ( TY_sales - LY_sales, LY_sales )
Best regards,
Yuliana Gu
Hi @cosminc ,
Please modify the formula as below:
YOY = VAR TY_sales = SUM ( Source[Sales] ) VAR LY_sales = CALCULATE ( SUM ( Source[Sales] ), FILTER ( ALLSELECTED ( Source ), Source[Year] = SELECTEDVALUE ( Source[Year] ) - 1 && Source[MonthName] = SELECTEDVALUE ( Source[MonthName] ) ) ) RETURN DIVIDE ( TY_sales - LY_sales, LY_sales )
Best regards,
Yuliana Gu
Thanks,
it works.
Cosmin
in addition i need if i filter salesman to work properly
previous expression with full calendar eas working only if salesman was not filtered or if that salesman had data for each month
thanks,
Cosmin
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |