Forum Discussion

FaridA's avatar
FaridA
New Member
3 years ago
Solved

Date in Measure Filter Not Working

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
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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