topic Re: DateAdd Not Working As Expected in DAX Commands and Tips

DateAdd Not Working As Expected

bkuhlmann — Fri, 15 Mar 2024 13:42:18 GMT

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?

Re: DateAdd Not Working As Expected

Greg_Deckler — Fri, 15 Mar 2024 13:47:12 GMT

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-TITHW/m-p/434008

Re: DateAdd Not Working As Expected

bkuhlmann — Fri, 15 Mar 2024 15:36:19 GMT

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!

Re: DateAdd Not Working As Expected

Greg_Deckler — Fri, 15 Mar 2024 18:11:24 GMT

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

or am I missing something?

Re: DateAdd Not Working As Expected

bkuhlmann — Mon, 18 Mar 2024 14:41:43 GMT

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!