Forum Discussion

bathbath's avatar
bathbath
Frequent Visitor
2 years ago

Slicer based on 445 columns

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