Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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
It should also be a total for each of the columns. Any idea how to create the report/formula ?
Solved! Go to Solution.
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.
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.
Hi,
What logic have you used for determining the Yes and No under the Active column of Table2? Please explain.
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.
And the last :-), also a total sum at the buttom of each column.
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.
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?
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |