Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello All,
I am new to power BI and working on an issue. My dataset is as below and have a filter on ClosedDate to include only Dates in Future. I have to ignore the ClosedDate filter if the Status is Ongoing. Please help.
ID Status ClosedDate filter on ClosedDate (now)
100 Initial 01/01/2025 Return
101 Ongoing 01/01/2021 --- this is also expected to Return as status is 'Ongoing'
103 Paused 01/02/2023 Return
104 Ongoing 01/02/2023 Return
105 Closed 01/01/2021
Solved! Go to Solution.
Hi @Meena ,
Please try below dax formula to create a measure and add it to table visual
Measure =
VAR cur_date =
NOW ()
VAR cur_status =
SELECTEDVALUE ( 'Table'[Status] )
VAR cur_closedate =
SELECTEDVALUE ( 'Table'[ClosedDate] )
RETURN
SWITCH (
TRUE (),
cur_closedate > cur_date, "Return",
cur_status = "Ongoing", "Return"
)
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Create a Calendar Table with a relationship (Many to One and Single) from the ClosedDate column of the Data Table to the Date column of the Calendar Table. Create a Table visual and drag ID/Status in there. Write this measure and drag it to the visual
Date value = min(Calendar[Date])
Measure = calculate(countrows(Data),filter(values(Calendar[Date]),([Date value]>today()&&Data[Status]<>"Closed")||Data[Status]="Ongoing"))
Hope this helps.
Hi @Meena ,
Please try below dax formula to create a measure and add it to table visual
Measure =
VAR cur_date =
NOW ()
VAR cur_status =
SELECTEDVALUE ( 'Table'[Status] )
VAR cur_closedate =
SELECTEDVALUE ( 'Table'[ClosedDate] )
RETURN
SWITCH (
TRUE (),
cur_closedate > cur_date, "Return",
cur_status = "Ongoing", "Return"
)
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.