Hi,
I have a boolean filed as shown below. This is a calculated column
Sample1_IsSamePeriodLY = IF('Sample1_Calendar date'[Date] >= DATE(YEAR(TODAY()), 1, 1), 1 &&
IF('Sample1_Calendar date'[Date] <= TODAY()-1, 1, 0))
I want this filter to be part of the my previous ytd calculation, however when i use this in visual or page level filters, it works fine and gives only true records.
if i use it inside dax like, it does not filter the true values. The main reason behind putting this in dax is i need to calculate Variance %, if i put visual level filters for %variance, the results are wrong, I need the filter in dax so that
the % calculation is accurate. Can you please look into it. PYTD, YTD and Variance all are measures.
PYTD Sample1 =
CALCULATE (
DISTINCTCOUNT(Sample1[activation_id]),SAMEPERIODLASTYEAR('Sample1_Calendar date'[Date]),
KEEPFILTERS(Sample1[paid_unpaid] ="Paid"),
KEEPFILTERS(Sample1[RealRegion_Sample1] <> "OTHER"),
KEEPFILTERS(Sample1[RealRegion_Sample1] <> BLANK())
('Sample1_Calendar date'[Sample1_IsSamePeriodLY] = TRUE())
)
YTD Sample1 =
CALCULATE (
DISTINCTCOUNT(Sample1[activation_id]),
KEEPFILTERS('Saample1_Calendar date'[Date]>= date(2021,01,01)),
keepfilters('Sample1_Calendar date'[Date] <= date(2021,12,31)),
KEEPFILTERS(Sample1[paid_unpaid] = "Paid"),
KEEPFILTERS(Sample1RealRegion] <> "ABC"),
KEEPFILTERS(Sample1[RealRegion]<> "OTHER"),
KEEPFILTERS(Sample1[RealRegion]<> BLANK()))
%Variance_Sample1 =
VAR __BASELINE_VALUE = [PYTD Sample1]
VAR __VALUE_TO_COMPARE = [YTD Sample1]
RETURN
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
