Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to get carts shipped YTD last year. I've tried each of the below without success. They either return a blank or the sum or last year or the total sum.
1. LY YTD =
VAR DataMaxDate =
CALCULATE ( MAX ( table_name[shipdate] ), ALL (table_name) )
RETURN
CALCULATE (
[YTD Carts],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( table_name[shipdate] ),
DATESBETWEEN ( table_name[shipdate], BLANK (), DataMaxDate )
)
)
)
2. LY YTD = CALCULATE(
SUM(
table_name[Carts]),
DATEADD(table_name[shipdate],-1,YEAR
)
)
3. LY YTD = CALCULATE (
SUM (
table_name[Carts] ),
FILTER (
table_name,
YEAR ( table_name[shipdate] ) = YEAR ( TODAY () )-1
&& DAY ( table_name[shipdate] ) <= YEAR(DAY(TODAY()
)
-1)
)
)
Returns
1. blank
2. sum of LY
3. inexplicably large number
New to DAX as you'll quickly see if you look at my recent posts. What am I missing?
Solved! Go to Solution.
Hi @KevinCP,
This may work if you have a relationship between the two tables and a [Dim_date] table since you may have multi shipments in the same day:
Create a Measuses:
1.Total Carts = SUM (table_name[Carts])
2.Total Carts LY = CALCULATE ([Toatal Carts], SAMEPERIODLASTYEAR(Dim_date[date]))
Hope help you
Regards
@Anonymous
Hi @KevinCP,
This may work if you have a relationship between the two tables and a [Dim_date] table since you may have multi shipments in the same day:
Create a Measuses:
1.Total Carts = SUM (table_name[Carts])
2.Total Carts LY = CALCULATE ([Toatal Carts], SAMEPERIODLASTYEAR(Dim_date[date]))
Hope help you
Regards
@Anonymous