Forum Discussion
slicing a table between two dates
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?
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'.
11 Replies
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.
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!!
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.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.
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.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.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'.
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.
