cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Need help on Filtering for time period and concatenating

Hi,

I am new to power bi , try to figure out a solution for a client for last 5 days.

This is a sample data. Appreciate your help. I tried with Matrix and table and both not working out. Need help

Input excel:

 enddate status product tracking 11/2/2022 Slipped Lenova ctr_1 12/10/2022 Slipped Microsoft ctr_1 3/10/2023 Slipped Microsoft ctr_2 3/30/2023 Ontrack Microsoft Ctr-5 4/10/2023 Ontrack Lenova ctr_3 4/17/2023 Risk Lenova ctr_4 5/31/2023 Risk Microsoft ctr-11 5/30/2023 Ontrack HP ctr-9 5/10/2023 Ontrack Lenova ctr_6 11/10/2023 Risk Microsoft ctr_12 12/10/2023 InProgress HP ctr_13

Expected outcome

Table is filtered for previous 4 - months from Current month and future 4 month)

First column grouped by Product ,

Due - Based on the endate column prefix std text is added and month is calculated grouped.

Total = total Count of tracking by product within the timeframe

status  = this is status by product and count in brackets (Text and Number)

2 REPLIES 2
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Due measure : =
VAR _before4months =
EOMONTH ( TODAY (), -4 ) + 1
VAR _currentmonth =
EOMONTH ( TODAY (), 0 )
VAR _after4months =
EOMONTH ( TODAY (), 4 )
VAR _filtertablebefore =
SUMMARIZE (
FILTER (
Data,
Data[enddate] >= _before4months
&& Data[enddate] <= _currentmonth
),
"@monthend", EOMONTH ( Data[enddate], 0 ),
"@monthname", FORMAT ( [enddate], "mmm" )
),
[@monthend],
[@monthname]
)
VAR _filtertableafter =
SUMMARIZE (
FILTER ( Data, Data[enddate] > _currentmonth && Data[enddate] <= _after4months ),
"@monthend", EOMONTH ( Data[enddate], 0 ),
"@monthname", FORMAT ( [enddate], "mmm" )
),
[@monthend],
[@monthname]
)
RETURN
IF (
HASONEVALUE ( Data[product] ),
"Due: "
& CONCATENATEX ( _filtertablebefore, [@monthname], "/ ", [@monthend], ASC )
& UNICHAR ( 10 ) & "Due: "
& CONCATENATEX ( _filtertableafter, [@monthname], "/ ", [@monthend], ASC )
)
``````

``````Count measure : =
VAR _before4months =
EOMONTH ( TODAY (), -4 ) + 1
VAR _currentmonth =
EOMONTH ( TODAY (), 0 )
VAR _after4months =
EOMONTH ( TODAY (), 4 )
VAR _filtertablebefore =
SUMMARIZE (
FILTER (
Data,
Data[enddate] >= _before4months
&& Data[enddate] <= _currentmonth
),
"@monthend", EOMONTH ( Data[enddate], 0 ),
"@monthname", FORMAT ( [enddate], "mmm" )
),
[@monthend],
[@monthname]
)
VAR _filtertableafter =
SUMMARIZE (
FILTER ( Data, Data[enddate] > _currentmonth && Data[enddate] <= _after4months ),
"@monthend", EOMONTH ( Data[enddate], 0 ),
"@monthname", FORMAT ( [enddate], "mmm" )
),
[@monthend],
[@monthname]
)
RETURN
IF (
HASONEVALUE ( Data[product] ),
COUNTROWS ( _filtertablebefore ) + COUNTROWS ( _filtertableafter )
)
``````

``````Status measure : =
VAR _before4months =
EOMONTH ( TODAY (), -4 ) + 1
VAR _currentmonth =
EOMONTH ( TODAY (), 0 )
VAR _after4months =
EOMONTH ( TODAY (), 4 )
VAR _filtertablebefore =
SUMMARIZE (
FILTER (
Data,
Data[enddate] >= _before4months
&& Data[enddate] <= _currentmonth
),
Data[status]
)
VAR _filtertableafter =
SUMMARIZE (
FILTER ( Data, Data[enddate] > _currentmonth && Data[enddate] <= _after4months ),
Data[status]
)
RETURN
IF (
HASONEVALUE ( Data[product] ),
MAXX ( _filtertablebefore, Data[status] ) & "("
& COUNTROWS ( _filtertablebefore ) & ")"
& UNICHAR ( 10 )
& MAXX ( _filtertableafter, Data[status] ) & "("
& COUNTROWS ( _filtertableafter ) & ")"
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

Thanks @Jihwan_Kim  . You are Awesome , this is cloe the solution i was looking for .

How do we add Color formating at "Status Measure" as it is one cell . Also in mesure can we exclude Null values

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors