Forum Discussion

bkuhlmann's avatar
bkuhlmann
Frequent Visitor
2 years ago

DateAdd Not Working As Expected

Hi Everyone - I'm using DATEADD to compare current QTD values against QTD values from the prior year. The SQLBI article linked below indicates that I should be able to breakout last year's QTD figures by month. While my formula is correctly returning the total value, when I try to break it out by month each month shows the same number

 

Differences between DATEADD and PARALLELPERIOD in DAX - SQLBI

 

My 2 tables are Calendar_CreateDate and Opportunity and they are joined in a bi-directional relationship, so I'm using CROSSFILTER to make it oneway for this measure bc DATEADD will not work otherwise. I'm using CALCULATETABLE to filter the calendar table to result all the dates from the start of the current quarter until today.

 

Prior Year (QTD)2 = 
CALCULATE(
    SELECTEDMEASURE(), 
    DATEADD(
        CALCULATETABLE(
            VALUES(Calendar_CreateDate[Create Date]), 
            CROSSFILTER(Opportunity[Opp Create Date],Calendar_CreateDate[Create Date], OneWay), 
            Calendar_CreateDate[Flag: CFQ (Create Date)] = "Y", 
            Calendar_CreateDate[Create Date] <= TODAY()
        ), 
        -1,
        YEAR
    )
)

 

Here is the result:

 
How come this isn't working for me?

4 Replies