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.
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)
KEEPFILTERS() does not work the same way a visual, page or report filter works. Using KEEPFILTERS in DAX - SQLBI
Show the expected outcome.
Hi,
Please find the pbix attached and the desired output is shown, I have used visual level filters and I am geeting desired % variance, but I want that the calc column(Sample1_IsSamePeriodLY) inside DAX? is there a way?
Sample1_IsSamePeriodLY = IF('Sample1_Calendar'[Date]>= DATE(YEAR(TODAY()), 1, 1), 1 &&
IF('Sample1_Calendar'[Date] <= TODAY()-1, 1, 0))
https://drive.google.com/file/d/1q2NjptMzZlFOU1DtDIRGZ0fHjQl97-X1/view?usp=sharing
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 |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |