Forum Discussion
create chart from start, end-date and total value
Hi,
I have the following data:
| Name | More Columns | Startdate | EndDate | Value |
| Project 1 | ... | 2020-01-05 | 2020-07-18 | 60 000 000 |
| Project 2 | ... | 2020-03-04 | 2020-06-01 | 55 000 000 |
Project 3 | ... | 2020-02-12 | 2020-12-21 | 165 000 000 |
| More projects |
I want to distribute all projects to a barchart with a time axis.
I thought about doing a crossjoin with all but that table would be huge if using only one maxstart and one max end.
Any thoughts?
9 Replies
patrickbender - Check out Open Tickets - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi, patrickbender
I am not very clear about your expected result. I assume that you want to calculate the number of 'Name' or the sum of 'Value' for specific period. I created data to reproduce your scenario.
Table:
Calendar(a calculated table):
Calendar = CALENDARAUTO()There is no relationship between two tables. You may create measures as below.
Count = CALCULATE( DISTINCTCOUNT('Table'[Name]), FILTER( ALL('Table'), 'Table'[Start Date]<=MAX('Calendar'[Date])&& 'Table'[End Date]>=MAX('Calendar'[Date]) ) ) Sum = CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Start Date]<=MAX('Calendar'[Date])&& 'Table'[End Date]>=MAX('Calendar'[Date]) ) )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.
Hi,
Sorry about the vauge question,I attach a pbix file with the desired result.
However I would also like to have that as a running total and not per month but my measuers doesn't seem to work.
I replied to a similar post using Power Query. It's here:
https://community.powerbi.com/t5/Power-Query/PowerQuery-M-instead-of-DAX/m-p/1347532#M42526
To get the 'amount per Day' add a column to divide [Omsättning] by the days between start and end (Do this before expanding the column with the list of dates).
After you expand the list column, you can get the running total in Power Query or DAX
-------------------
To use DAX to create the table, you can start with:
T2 = GENERATE(Projekt, VAR _start = Projekt[Start] VAR _end = Projekt[**bleep**] RETURN CALENDAR(_start, _end) )Filter the Projekt table for 'Order' in the above syntax if required
Hi,
Sorry about the vauge question,I attach a pbix file with the desired result.
However I would also like to have that as a running total and not per month but my measuers doesn't seem to work.
