思路:
新日期表用作横坐标轴
去除旧日期表筛选
新日期表区间限定measure并与旧日期表建立关系
measure eg.
--显示1年每月数据
Amt.AllMonth =
--思路:删除原来的年月筛选器,替换为新的年月筛选器;新日历表;动态挂载
VAR startd =
MINX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[FiscalYearName] = SELECTEDVALUE ( 'Calendar'[FiscalYearName] )
),
'Calendar'[Date]
)
VAR maxd =
MAX ( 'Calendar'[Date] )
VAR period_ =
DATESBETWEEN ( Calendar_Pre[Date], startd, maxd )
VAR value_ =
CALCULATE (
[Amt],
REMOVEFILTERS ( 'Calendar' ),
KEEPFILTERS ( period_ ),
USERELATIONSHIP ( 'Calendar'[Date], Calendar_Pre[Date] )
)
RETURN
value_
--显示2年对比
Amt.2FY =
--去除原来的日期筛选,保留新的筛选器
--财年-1,月份不变
--月份设置多选 ,如果多选/不选怎么办
VAR fiscaly =
SELECTEDVALUE ( 'Calendar'[FiscalYear] )
VAR fiscalpy = fiscaly - 1
VAR month_ =
VALUES('Calendar'[Month])
VAR value_ =
CALCULATE (
[Amt],
REMOVEFILTERS ( 'Calendar' ),
FILTER (
Calendar_Pre,
Calendar_Pre[FiscalYear]
IN { fiscaly, fiscalpy }
&& Calendar_Pre[Month] in month_
),
USERELATIONSHIP ( 'Calendar'[Date], Calendar_Pre[Date] )
)
RETURN
value_
--显示2年每月对比
Amt.2FY.AllMonth =
--去除原来的日期筛选,保留新的筛选器
--财年-1
--月份全都要
VAR fiscaly =
SELECTEDVALUE ( 'Calendar'[FiscalYear] )
VAR fiscalpy = fiscaly - 1
VAR FM_ =
MAXX(VALUES('Calendar'[FiscalMonthnum]),'Calendar'[FiscalMonthnum])
var period_ = FILTER (
Calendar_Pre,
Calendar_Pre[FiscalYear]
IN { fiscaly, fiscalpy }
&& Calendar_Pre[FiscalMonthnum] <= FM_
)
VAR value_ =
CALCULATE (
[Amt],
REMOVEFILTERS ( 'Calendar' ),
KEEPFILTERS(period_),
USERELATIONSHIP ( 'Calendar'[Date], Calendar_Pre[Date] )
)
RETURN
value_