slicing a table between two dates

[nick9one1]

I have a table of assets. Each asset has a start and end date (when it was new and when it broke or stopped working).
My report has a date dimewnsion table (dim_date), that I use in the slicer to select a quarter. 

For the visual I want to show all asswets that are still 'alive' for the date quarter that is selcted. How can I do this?

[V-yubandi-msft]

Hi @nick9one1 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft · Accepted solution]

Hi @nick9one1 ,

Thank you for engaging with the Microsoft Fabric Community. I tested your requirement with some dummy data and successfully replicated a solution where the bar chart displays all assets that were active during the date range selected via the slicer from the dim_date table.

1. The visual uses this measure as a filter (IsAssetAlive=1) to only include those assets.
2. The bar chart then shows the count of AssetID by AssetName for only the alive assets in the selected time range.
FYI:

I’ve attached a sample .pbix  file that demonstrates this behavior. Could you please review and confirm if this aligns with your expected outcome?

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.

[Anonymous]

Hi @nick9one1,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[Anonymous]

Hi @nick9one1,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[PaulDBrown]

@nick9one1 

This thread was tackling a similar problem.

A slicer filtering between 2 dates 

See if the method helps. I can try to create a working soultion but would appreciate some sample non-sensitive data and a mockup of the expected outcome.

[Kishore_KVN]

Hello @nick9one1 ,

You can create a measure which should be similar like this:
Counting = calculate(count(assets), Allselected(academic quarter), 'Assets'[live_status]="alive")

please change column names and other references as per your data tables and columns. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[nick9one1]

thanks. Maybe I didnt explain that correctly.
By 'alive' I mean the asset start date is <= the current slicer and asset end date is >= the current slicer. there is no column called alive. 

e.g a mirror was installed 01/01/2024 and was broken on the 10/10/2024.
If I select a slicer quarter between those date ranges the mirror should appear in the visual. If I select a quarter after it was broken is should not appear.  

[Anonymous]

Hi @nick9one1,

Thank you for reaching out in Microsoft Community Forum.

Thank you @Kishore_KVN  for the helpful response.

Please follow below steps to resolve the issue;

1.Create the measure with the below DAX;

Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
MAX('Assets'[StartDate]) <= MaxSelectedDate &&
(ISBLANK(MAX('Assets'[EndDate]) || MAX('Assets'[EndDate]) >= MinSelectedDate)),
1,
0
)

2.Add this measure to the visual filter pane:

--> Set it to show only when Is Asset Active = 1.

This will display assets whose active periods overlap with the selected quarter — for example, a mirror installed in Jan 2024 and broken in Oct 2024 will only appear in visuals when you select quarters that fall within that time range.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[nick9one1]

thanks. I have tried but it's not quite working. 'is asset active' always returns 0

Here you can see I have included the  'is asset active' measure in a table. The min and max dates from the date table asre also shown in cards. 
the first row has an asset that should show as '1'

[Anonymous]

Hi @nick9one1,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps to fix the issue;

1.Create a calculated column (not a measure) in your Assets table with this DAX:

Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
'Assets'[StartDate] <= MaxSelectedDate &&
(
ISBLANK('Assets'[EndDate]) || 'Assets'[EndDate] >= MinSelectedDate
),
1,
0
)

2.Try this measure instead, which respects the current slicer context and evaluates assets dynamically:

Is Asset Active =
VAR MinSelectedDate = MIN('dim_date'[Date])
VAR MaxSelectedDate = MAX('dim_date'[Date])
RETURN
IF (
'Assets'[StartDate] <= MaxSelectedDate &&
(
ISBLANK('Assets'[EndDate]) || 'Assets'[EndDate] >= MinSelectedDate
),
1,
0
)

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[nick9one1]

These answers seem to be made with a fudemental misunderstanding of powerBi, I wouldnt be surprised if they are Ai coplot generated. 

Your first suggestion of a calculated column doesnt work because it cannot refernece the sliced table. It simply looks at the min and max dates in the whole date table. 

The second suggestion does not work because you cannot refernce the column 'Assets'[EndDate]) inside RETURN