Date in Measure Filter Not Working

[FaridA]

Hi, I am stumped on this issue. I have a single table called 'WIPTransactions'. I am using measures to calculate the SUMS of WIPAmount based on different time periods. The table starts with date 1/1/2022 and ends today. To SUM today's YTD total I use the following measure which works:

YTD Current Actual =

CALCULATE(

    -SUM(WIPTransactions[WIPAmount]),

    FILTER(WIPTransactions,YEAR(WIPTransactions[WIPDate])=2023),

    FILTER(WIPTransactions,MONTH(WIPTransactions[WIPDate])>=1),

    FILTER(WIPTransactions,WIPTransactions[WIPDate]<=(LASTDATE(WIPTransactions[WIPDate])))

    )

I would like to calculate the year-to-date for the prior year as well, however nothing seems to work for me. I used the following measure with no success (The results are just blank):

YTD Prior Actual =

CALCULATE(

    -SUM(WIPTransactions[WIPAmount]),

    FILTER(WIPTransactions,YEAR(WIPTransactions[WIPDate])=2022),

    FILTER(WIPTransactions,MONTH(WIPTransactions[WIPDate])>=1),

    FILTER(WIPTransactions,WIPTransactions[WIPDate]<=DATEADD(LASTDATE(WIPTransactions[WIPDate]),-1,YEAR))

  )

Any help as to what I am doing wrong would be appreciated. Thanks

[amitchandak · Accepted solution]

@FaridA , You should use date table and Time intellignece 

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[FaridA]

This worked, thank you!