Skip to main content
cancel
Showing results for 
Search instead 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

Reply
swasim
Helper I
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: 

 

enddatestatusproducttracking
11/2/2022SlippedLenovactr_1
12/10/2022SlippedMicrosoftctr_1
3/10/2023SlippedMicrosoftctr_2
3/30/2023OntrackMicrosoftCtr-5
4/10/2023OntrackLenovactr_3
4/17/2023RiskLenovactr_4
5/31/2023RiskMicrosoftctr-11
5/30/2023OntrackHPctr-9
5/10/2023OntrackLenovactr_6
11/10/2023RiskMicrosoftctr_12
12/10/2023InProgressHPctr_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.

 

swasim_0-1679832579715.png

 

 

 

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1679838035291.png

 

 

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 ) & ")"
    )

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.


Go to My LinkedIn Page


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

swasim_0-1679847050466.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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