Using two date dimensions not working

gspollock — Mon, 01 May 2023 20:31:02 GMT

I want to have a graph with two lines showing sales data from the prior and current year. I want one line to just be a straight line across which is the prior year sales multiplied by a fixed number representing a percent increase. The other line will be the cumulative sales to date by month for the current year.

The report has a table which shows the current year-to-date Average Daily Sales, and the prior year-to-date Average Daily Sales as of the same month in the prior year. Users can select a month from a date slicer, based on the date dimension DIM_PERIOD_GL_DATE, in order to view cumulative sales data as of the selected month in the current and prior years. For example sales data through April of the current year and April of last year, February of the current year and last year etc.

I don't want the graph to be affected by which month is selected in the slicer. I created a second date dimension DIM_PERIOD_GL_DATE2 to be used by the graph and defined a measure like this for the straight line showing the prior year sales with a 3% increase:

CFY Sales Goal = var yr = CALCULATE(

MAX(DIM_PERIOD_GL_DATE2[FISC_YR_NUM]),

FILTER( ALL(DIM_PERIOD_GL_DATE2),DIM_PERIOD_GL_DATE2[Date] = TODAY()-1)

)
var pysls = CALCULATE(

FACT_SLS[Sales AMT], FILTER( ALL(DIM_PERIOD_GL_DATE2),DIM_PERIOD_GL_DATE2[FISC_YR_NUM] = yr-1 )

)
return pysls*1.03

However, when I select a month from the slicer that uses DIM_PERIOD_GL_DATE it is

Changing the value of CFY Sales Goal in the visual
Shows a value only for the selected month even though the date in the visual is from DIM_PERIOD_GL_DATE2.

Even if the measure was based on DIM_PERIOD_GL_DATE selecting a month shouldn't change the value of the measure. As expected the value doesn't change when I select various months from a slicer that uses DIM_PERIOD_GL_DATE2 (as long a month from the current year is selected).

Why is the date slicer that uses DIM_PERIOD_GL_DATE affecting the visual?

Re: Using two date dimensions not working

Wilson_ — Tue, 02 May 2023 02:57:38 GMT

Hello,

Your questions would be much easier to answer if you provided your pbix file (or at least a sample sanitized version). 🙂

topic Using two date dimensions not working in DAX Commands and Tips

Using two date dimensions not working

Re: Using two date dimensions not working