Need help on Filtering for time period and concatenating

[swasim]

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)

Let me know if u need more info, If solved , please attach the pbix file.

[Jihwan_Kim]

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 (
        ADDCOLUMNS (
            FILTER (
                Data,
                Data[enddate] >= _before4months
                    && Data[enddate] <= _currentmonth
            ),
            "@monthend", EOMONTH ( Data[enddate], 0 ),
            "@monthname", FORMAT ( [enddate], "mmm" )
        ),
        [@monthend],
        [@monthname]
    )
VAR _filtertableafter =
    SUMMARIZE (
        ADDCOLUMNS (
            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 (
        ADDCOLUMNS (
            FILTER (
                Data,
                Data[enddate] >= _before4months
                    && Data[enddate] <= _currentmonth
            ),
            "@monthend", EOMONTH ( Data[enddate], 0 ),
            "@monthname", FORMAT ( [enddate], "mmm" )
        ),
        [@monthend],
        [@monthname]
    )
VAR _filtertableafter =
    SUMMARIZE (
        ADDCOLUMNS (
            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 ) & ")"
    )

[swasim]

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