create chart from start, end-date and total value

[patrickbender]

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? 

[v-alq-msft]

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.

[Syndicate_Admin]

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.

Link to file 

@v-alq-msft @Greg_Deckler 

[patrickbender]

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.

Link to file 

@v-alq-msft @Greg_Deckler 

[Syndicate_Admin]

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

[HotChilli]

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

[Syndicate_Admin]

Almost there now,

I managed to get the running total but can't divide the new table properly.

For the status "Budget" I want to show that on a line chart and for all other I want to show a stacked bar chart.

As soon as I add the last filter to my calculation it breaks.

I attached the file here:

Link to file 

@HotChilli @v-alq-msft @Greg_Deckler 

[patrickbender]

Almost there now,

I managed to get the running total but can't divide the new table properly.

For the status "Budget" I want to show that on a line chart and for all other I want to show a stacked bar chart.

As soon as I add the last filter to my calculation it breaks.

I attached the file here:

Link to file 

@HotChilli @v-alq-msft @Greg_Deckler 

[HotChilli]

Some unusual things going on here.  Quite advanced DAX combined with using the built-in date tables and an unrelated Calendar table which doesn't appear to be used and a hidden table in dataview (Result) which is being used.

Anyway, are we talking about the lower line/column chart?  And it's using T2 data.

I don't really have time to look in detail at the measures but I suggest you make a copy of the measure 'Lopande summa......' and replace the last Filter clause with:

FILTER(T2,	'T2'[Status] = "Budget")

Put this new measure in the Line Values.  Please test.

[Greg_Deckler]

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