Diff Between Not Calculating

[cbtekrony]

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: 

ACV Diff Between TY LY = SUM('Divya ACV'[Product Value]) - [ACV Product Value LY]

ACV Product Value LY = CALCULATE (SUM('Divya ACV'[Product Value]),SAMEPERIODLASTYEAR('Calendar Table'[Date]))

Clearly the product value is duplicating.

[Greg_Deckler]

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...

[cbtekrony]

This is great, I will be referencing this when I get to the YOY % difference!  I've bookmarked it for sure!

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach:

1. Create a Calendar Table and write calculated column formulas to extract Year, Month name and Month number.  Year = Year(Calendar[Date]), Month name = FORMAT(Calendar[Date],"mmmm") and Month number = MONTH(Calendar[Date[)
2. Sort the Month Name column by the Month number
3. Build a relationship from the Date column in your Divya ACV Table to the Date column of your Calendar Table
4. To your visual, drag Year and Month name from the Calendar Table
5. Write these measures

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.

[amitchandak]

@cbtekrony 

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"))

[cbtekrony]

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!

[Ashish_Mathur]

Hi,

Whom are you asking that question?  Show data in a Table and also the expected result.

[cbtekrony]

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