Forum Discussion

fellin's avatar
fellin
Regular Visitor
5 years ago
Solved

Dynamically calculate Count of Rows for a specific Year/Month

Hi all,

I'm pretty new to PowerBi and can't find anything which pushes me in the right direction. My problem:

I have a table with diffrent Actions with a start and a end date, like this below:

Action-IdStart DateEnd DateDepartment
101.01.202020.02.2020A
202.01.20205.01.2020B
310.10.202012.12.2020B

And i want to create a Line and column chart, with Month on the x-Axis (drilldown diffrent years) and on the y-axis i want to visualize three things:

1) How many new Actions were started in a specific Month?

2) How many Actions were completed every month?

3) And how many Actions are still open.

 

My first Idea was to create a calculated table, like this one: (example for one Month)

Month/YearCumulative opened ActionsCumulative Closed ActionsCount of New ActionsCount of Closed ActiosDifference between Col 2 and 3
Jan 2020
CALCULATE(COUNTROWS(Table1),FILTER(ALLSELECTED(Table1),Table[StartDate]<='Table2'[Column1]))
CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[CompletedDate]<='Table2'[Column1]))
Check if Start Date is same as in Col 1Check if Completion Date is like Col 1 

 

I got the expected results, But it didn't work as it is a static table and i need it to be filtered by slicers on my page so that you cant filter it eg. for department.  Is there a way to reach the same result with measures, which can filtered dynamically? How do i create a line column chart which icludes everything? Maybe someone can help my to get on the right path.

 

Thanks a lot!

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, fellin 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calcualted table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YearMonth",
    YEAR([Date])*100+MONTH([Date])
)

     

    You may create three measures as below.

    Count Start = 
var ym = MAX('Calendar'[YearMonth])
return
COALESCE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            (YEAR([Start Date])*100+MONTH([Start Date]))=ym
        )
    ),0
)
    Count End = 
var ym = MAX('Calendar'[YearMonth])
return
COALESCE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            (YEAR([End Date])*100+MONTH([End Date]))=ym
        )
    ),0
)
    Count Open = 
var x = MAX('Calendar'[YearMonth])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Flag",
    var t = 
    SELECTCOLUMNS( 
        ADDCOLUMNS(
            CALENDAR(
                [Start Date],
                [End Date]
            ),
            "ym",
            YEAR([Date])*100+MONTH([Date])
        ),
        "ym",[ym]
    )
    return 
    IF(
       x in t,
       1,0
    )
)
return
COALESCE(
    COUNTROWS(
        FILTER(
            tab,
            [Flag]=1
        )
    ),
    0
)

     

    Result:

     

    Best Regards

    Allan

     

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

     

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, fellin 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calcualted table):

    Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YearMonth",
    YEAR([Date])*100+MONTH([Date])
)

     

    You may create three measures as below.

    Count Start = 
var ym = MAX('Calendar'[YearMonth])
return
COALESCE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            (YEAR([Start Date])*100+MONTH([Start Date]))=ym
        )
    ),0
)
    Count End = 
var ym = MAX('Calendar'[YearMonth])
return
COALESCE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            (YEAR([End Date])*100+MONTH([End Date]))=ym
        )
    ),0
)
    Count Open = 
var x = MAX('Calendar'[YearMonth])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Flag",
    var t = 
    SELECTCOLUMNS( 
        ADDCOLUMNS(
            CALENDAR(
                [Start Date],
                [End Date]
            ),
            "ym",
            YEAR([Date])*100+MONTH([Date])
        ),
        "ym",[ym]
    )
    return 
    IF(
       x in t,
       1,0
    )
)
return
COALESCE(
    COUNTROWS(
        FILTER(
            tab,
            [Flag]=1
        )
    ),
    0
)

     

    Result:

     

    Best Regards

    Allan

     

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

     

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    First step is to add a dates/calendar table to your data model.

    Then you want to learn about active and inactive relationships, and USERELATIONSHIP() modifiers.

    And after that you can write measures that achieve what you need.