DateAdd problem with Quarter

[chrisrobaciu]

Hello,

I have a weird problem which has been perplexing me for a couple of days:

I am trying to compare two (balance sheet) values over two periods based on the quarter end period that I select (using SWITCH):

1. Current period vs. prior year end (e.g. September vs. December)

2. Current period vs. prior quarter end (e.g. September vs. June).

All seems to work fine, EXCEPT FOR when I select the month of June to compare with the prior quarter end (which would be March) and the prior quarter appears to be blank. All other iterations work fine.

 The metric to compare is calculated as follows:

1. Actuals Measure: BS Actuals (base) = SUM('Summary Actuals'[Actual])

2. Selected Period Measure: BS Actuals = -CALCULATE([BS Actuals (base)],

FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=SELECTEDVALUE('Calendar'[Month End])))

where "SELECTEDVALUE" is selected using a slicer.

3. Prior period comparative measure: 

BS Actuals (Comparative) = SWITCH('Scenario BS Comparison'[SelectedBSComp],

1, CALCULATE('BS Measures'[BS Actuals],DATEADD(ENDOFYEAR('Calendar'[Month End]),-1,YEAR)),

2, CALCULATE('BS Measures'[BS Actuals],DATEADD(ENDOFQUARTER('Calendar'[Month End]),-1,QUARTER)))

where the switch is used to flip between "vs. prior year" (works fine) and "vs. prior quarter" (works fine except for June vs. March).

The relevant relationships are below (month end -> month end)

Supporting screenshots showing my issue:

1. Working fine (vs. prior year)

2. Working fine (vs. prior quarter, sept vs. June)

3. Not working 😞 : prior quarter, (June vs. March), displays blank in BS comparative.

Many thanks in advance for your kind support (and thanks to all the other posts, I use this forum a lot!)

[amitchandak]

@chrisrobaciu , Only use date

CALCULATE('BS Measures'[BS Actuals],DATEADD(ENDOFQUARTER('Calendar'[Date]),-1,QUARTER)))

or

CALCULATE([BS Actuals],DATEADD(('Calendar'[Date]),-1,QUARTER)))

[chrisrobaciu]

Hello @amitchandak , thank you for your quick reply and your help.

Unfortunately that didn't fix the issue that I have. When I change to date, it keeps the problem but introduces another problem (it removes the cumulative-to-date aspect of the comparative period).