Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a date table in Power BI which uses 445 Week Number.
I want to create my measure based on the slicer such that:
First Slicer: Year (2022), Month (4) and Week (3)
Second Slicer: Year (2022 or any other year), Any Month, Any week.
My measure should give me all the data between First Slicer and 2nd Slicer. How do I code that measure in DAX?
Attached is the pic of example.
I tried this: Where I created copy of this date table but in the return statement, I am running into issue. I need to reference both tables with condition. How do I achive that?
Or other alternative is : If I try to use just one table and someone selects something from slicer 1 then how do I make sure that I can choose whatever from slicer 2 (coming from the same table)?
Lead Count Period 1 =
VAR StartDate =
CALCULATE(
MIN('DIM_Date_WO_Lead_Created'[Date_WO_Lead_Created_SK]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Year_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Month_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Week_Num])
)
VAR EndDate =
CALCULATE(
MAX('DIM_Date_WO_Lead_Created (2)'[Date_WO_Lead_Created_SK]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Year_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Month_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Week_Num])
)
RETURN
CALCULATE(
SUM(FACT_Lowes_Sales_Leads[Lowes_Lead_Count]),
FILTER(
ALL('DIM_Date_WO_Lead_Created'),
'DIM_Date_WO_Lead_Created'[Calendar_Date] >= StartDate &&
'DIM_Date_WO_Lead_Created'[Calendar_Date] <= EndDate
)
)
Thanks,
RK
@bathbath , for that you need two such tables and one of them need to be disconnected
Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |