Forum Discussion

TrulsB's avatar
TrulsB
Frequent Visitor
6 years ago
Solved

Count rows on selected months

I have a  sharepoint list with a lot of projects. I do aloso have a date table with date, week, monty, year, quartal eg.  The columns in the sharepoint list is Projectname (text),  Start date (date)...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Since i have not understood the logic of the Yes/No, i have not been able to generate that column.  You may download my PBI file from here.

    Hope this helps.

  • dax's avatar
    dax
    6 years ago

    Hi TrulsB,

    You could refer to Ashish_Mathur 's file, if you want to get total in column and row, you could turn subtotal button like below

    If you want to get start date- stop date in last column, you could try to use below measure to replace measure in above file

    Measure =
IF (
    HASONEVALUE ( 'Attribute order'[Attribute] ),
    DISTINCTCOUNT ( Data[Projectname] ) + 0,
    CALCULATE (
        DISTINCTCOUNT ( Data[Projectname] ),
        FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Start date" )
    )
        - CALCULATE (
            DISTINCTCOUNT ( Data[Projectname] ),
            FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Stop date" )
        ) + 0
)

    Best Regards,
    Zoe Zhi

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

     

TrulsB's avatar
TrulsB
Frequent Visitor
6 years ago

The column is a sharepoint yes/no field.  

 

The table 1, the table with the dates is the Sharepoint list, and the last column  indicate if the project is active or not.  It should be possible to select only active projects(yes),  only inactive prosjects (no) or boths. 

 

The table 2 with the sum, is the report i need to create in Power BI, making a sum of how many projects that has it dates within each period, and a  total sum for each column.  The period interval is in the example in table 2 set to month, but i like to use a date table so i can select the report (table 2) on days, weeks,quartal, months , yrear - even on a date interval (eg. 1.5.2019 (1st may 2019) to 15.10.2019 (15th  october 2019).

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Since i have not understood the logic of the Yes/No, i have not been able to generate that column.  You may download my PBI file from here.

Hope this helps.

  • TrulsB's avatar
    TrulsB
    Frequent Visitor
    6 years ago

    Thank you for the file :-).  

    The Active field indicates if the project is active or not, and i like to select  active and inactive prosject, only active and only inactive. I like to select like this. 

     

    Active

    X Yes

     X No

     

  • TrulsB's avatar
    TrulsB
    Frequent Visitor
    6 years ago

    Is it possible to add an additional column at the end  in the result that is the difference between the "Start date" column, and "Stop date" column?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Started projects - Stopped projects or Stopped projects - Started projects could lead to negative numbers well.  Are you OK with that?  Also, should it be Started projects - Stopped projects or Stopped projects - Started projects?

  • TrulsB's avatar
    TrulsB
    Frequent Visitor
    6 years ago

    And the last :-), also a total sum at the buttom of each column.

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi TrulsB,

      You could refer to Ashish_Mathur 's file, if you want to get total in column and row, you could turn subtotal button like below

      If you want to get start date- stop date in last column, you could try to use below measure to replace measure in above file

      Measure =
IF (
    HASONEVALUE ( 'Attribute order'[Attribute] ),
    DISTINCTCOUNT ( Data[Projectname] ) + 0,
    CALCULATE (
        DISTINCTCOUNT ( Data[Projectname] ),
        FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Start date" )
    )
        - CALCULATE (
            DISTINCTCOUNT ( Data[Projectname] ),
            FILTER ( 'Attribute order', 'Attribute order'[Attribute] = "Stop date" )
        ) + 0
)

      Best Regards,
      Zoe Zhi

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