Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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
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
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:
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:
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.