I want to show the current period and previous period in the same line chart. The problem is that I need to be able to use slicers. For example
-
I am plotting an entire year, but if I want to select one specific month, the chart should display both the current and last year period
-
Due to the filter context if I select one specific month or date, the previous year calculation defaults to zero
-
I cannot use the built-in formulas because they do not support row level security.
CY sales =
var selectedYear = SELECTEDVALUE(sales[date],YEAR(TODAY()))
RETURN
CALCULATE(
SUM(sales[date]),
sales[year] = selectedYear)PY sales =
var selectedYear = SELECTEDVALUE(sales[date],YEAR(TODAY()))
RETURN
CALCULATE(
SUM(sales[date]),
sales[year] = (selectedYear-1))
1
you can try this
- create a date table and use that to filter
Table 2 = DISTINCT(‘Table'[date])
yearmonth = year(‘Table 2′[date])&FORMAT(‘Table 2′[date],”mmm”)
-
create a measure and add this measure to visual filter and set to 1
Measure =
VAR _max =
MAX ( ‘Table 2′[date] )
VAR _min =
MIN ( ‘Table 2′[date] )
RETURN
IF (
MAX ( ‘Table'[date] ) >= _min
&& MAX ( ‘Table'[date] ) <= _max
|| MAX ( ‘Table'[date] ) >= EDATE ( _min, -12 )
&& MAX ( ‘Table'[date] ) <= EDATE ( _max, -12 ),
1
)