Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Hello,
Your questions would be much easier to answer if you provided your pbix file (or at least a sample sanitized version). 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |