DateAdd Not Working As Expected

[bkuhlmann]

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?

[Greg_Deckler]

@bkuhlmann You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

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

[bkuhlmann]

Thanks Greg! I almost have your "Hard Way" working for me, but I'm struggling to return the correct MaxYear...I don't want the max year from the calendar table, rather I want the current year. I'm trying to filter the table to return the current year from my calendar table by using filtering it down based on Create Date = TODAY() but I'm struggling to return a single value to the variable. I've tried numerous functions to no avail. Thanks in advance!

[Greg_Deckler]

@bkuhlmann Why not just Create Year = YEAR(TODAY()) 

or am I missing something?

[bkuhlmann]

I was trying to use my calendar table because our fiscal year does not match our calendar year, and I also need to get both the FY and FQ. I could recreate those formulas in the measure but would prefer to pull from the calendar table instead.

Is there a way to get the FY and FQ for "today" from my calendar table instead?

Thanks for your help!