cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TrulsB
Frequent Visitor

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), Stop date (date), Closed date(date), Active (yes/no)

project-powerbi.JPG

I like to create report, where i can select on week, month, year or on a date interval, and count the rows with the date in the selected period  If i select may 2019, i like to count how many item has start date in may 2019, how many has close date in may 2019 and how many that has closed date in may 2019 - and also so i can select  if the projects are active, not active ot both. 

 

The result shoul be like this

project2-powerbi.JPG

It should also be a total for each of the columns.  Any idea how to create the report/formula ?  

 

 

 

2 ACCEPTED SOLUTIONS

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

dax
Community Support
Community Support

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

462.PNG

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.

 

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User III
Super User III

Hi,

What logic have you used for determining the Yes and No under the Active column of Table2?  Please explain.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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).

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

dax
Community Support
Community Support

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

462.PNG

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.

 

View solution in original post

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?

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

It is a Sharepoint yes/no field. 

amitchandak
Super User IV
Super User IV

Try creating formula's like this

Open Month = CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=MAXx('Date',ENDOFMONTH('Date'[Date Filer]))
&& Sales[Sales Date]>=Minx('Date',startOFMONTH('Date'[Date Filer]))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors