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.
My Diff Between Calc is presenting zeros:
Year | Product Category | Product Value | ACV Product Value LY | ACV Diff Between TY LY |
12/31/2011 0:00 | A | $131,282.70 | $131,282.70 | $0.00 |
12/31/2012 0:00 | A | $1,397,633.70 | $1,397,633.70 | $0.00 |
12/31/2013 0:00 | A | $2,472,531.60 | $2,472,531.60 | $0.00 |
12/31/2014 0:00 | A | $4,010,372.20 | $4,010,372.20 | $0.00 |
12/31/2015 0:00 | A | $5,295,672.70 | $5,295,672.70 | $0.00 |
12/31/2016 0:00 | A | $6,553,524.60 | $6,553,524.60 | $0.00 |
12/31/2017 0:00 | A | $7,571,281.70 | $7,571,281.70 | $0.00 |
12/31/2018 0:00 | A | $8,999,105.40 | $8,999,105.40 | $0.00 |
12/31/2019 0:00 | A | $9,793,244.80 | $9,793,244.80 | $0.00 |
12/31/2020 0:00 | A | $10,312,240.56 | $10,312,240.56 |
DAX is:
Solved! Go to Solution.
Hi,
Try this approach:
Total value = SUM('Divya ACV'[Product Value])
ACV Product Value LY = CALCULATE ([Total value],SAMEPERIODLASTYEAR('Calendar'[Date]))
ACV Diff Between TY LY = [Total value] - [ACV Product Value LY]
Hope this helps.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
This is great, I will be referencing this when I get to the YOY % difference! I've bookmarked it for sure!
Hi,
Try this approach:
Total value = SUM('Divya ACV'[Product Value])
ACV Product Value LY = CALCULATE ([Total value],SAMEPERIODLASTYEAR('Calendar'[Date]))
ACV Diff Between TY LY = [Total value] - [ACV Product Value LY]
Hope this helps.
Your Calendar Table should be marked as a Date table. Especially when join is on Non date columns.
Try following
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Thank you.
I found needed to create a new measure to in my measures table for the Product Value and use the following general syntax:
ACV Product Value LY = CALCULATE ([Total value],SAMEPERIODLASTYEAR('Calendar'[Date]))
I also pulled the year from my date table instead of my table from the original data pull.
This brought me to a new issue. So for YOY on ACV (Annual Contract Value), for multiple years I found that I need to be pulling a simple subtraction between the earliest and latest years instead of summing up all of the changes YOY for each date.
For example, the ACV difference between 2012 and 2014 should be 2.6 (2014 ACV of 4.0 - 2012 ACV of 1.4) instead of adding the ACV difference of 2012, 2013, and 2014.
If you know what language I need to complete for this let me know, otherwise I will repost a new question. Thanks!
Hi,
Whom are you asking that question? Show data in a Table and also the expected result.
The goal is for me to present a dynamic visual card that changes based on the years selected. I've bolded the information needed from the table above - see below. The desired result is in bold. The last two columns illustrate the differences in calculations. I believe I have to do some type of min/max year language to get to my ideal answer.
Year | Product Category | ACV | ACV Product Value LY | ACV Diff Between TY LY | Current Answer | Ideal Answer | |
2011 | A | $131,282.70 | $131,282.70 | Looking for difference in ACV from 2012-2014 | |||
2012 | A | $1,397,633.70 | $131,282.70 | $1,266,351.00 | |||
2013 | A | $2,472,531.60 | $1,397,633.70 | $1,074,897.90 | |||
2014 | A | $4,010,372.20 | $2,472,531.60 | $1,537,840.60 | $3,879,089.50 | $2,612,738.50 | |
2015 | A | $5,295,672.70 | $4,010,372.20 | $1,285,300.50 | (sum of ADV Diff 3 yrs) - Column 5 | (subtracts ACV Diff between 2014 and 2012) - Column 3 | |
2016 | A | $6,553,524.60 | $5,295,672.70 | $1,257,851.90 | |||
2017 | A | $7,571,281.70 | $6,553,524.60 | $1,017,757.10 |
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 |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |