adhoc reporting in power bi

0 votes
I'm trying to do adhoc reporting but I'm stuck somewhere. So i have a start date and and end date and need to consider those dates to calculate total number of seconds and percentage. But the problem is sometimes the start date is considered before the start date and end date is after the actual end date. I want only the actual/original start and end date to be considered for the calculation. Is that possible?
Oct 22, 2018 in Power BI by shubham
• 6,480 points
175 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Yse a DAX measure to calculate the sum of the duration for any events that fall on a given date. You can do this using a disconnected date table. 

For example, if you have a disconnected date table called Date, and your event table is called Event, you can have a measure such as:

Filtered Duration =
CALCULATE (
    SUMX (
        Event,
        DATEDIFF (
            MAX ( MIN ( 'Date'[Date] ), Event[StartDateTime] ),
            MIN ( MAX ( 'Date'[Date] ), Event[EndDateTime] ),
            SECOND
        )
    ),
    FILTER (
        'Event',
        'Event'[StartDateTime] <= MAX ( 'Date'[Date] )
            && 'Event'[EndDateTime] >= MIN ( 'Date'[Date] )
    )
)
answered Oct 22, 2018 by Hannah
• 14,040 points

Related Questions In Power BI

0 votes
1 answer

Programmatically sign in - power bi

Basically: POST request to: https://login.microsoftonline.com/common/oauth2/token Body, form-url-encoded: grant_type: "password" scope: "openid" resource: "https://analysis.windows.net/powerbi/api" client_id: ...READ MORE

answered Sep 19, 2018 in Power BI by Kalgi
• 35,640 points
34 views
0 votes
1 answer

create interactive R plots in Power BI

 Right now Power BI only supports render ...READ MORE

answered Sep 20, 2018 in Power BI by Kalgi
• 35,640 points
23 views
0 votes
1 answer

Power BI Reporting Server - Portal Administrator Functionality

I think you need to link it ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 35,640 points
16 views
0 votes
1 answer

Can I create a Power BI report in Office 365 without my locally installed Office Excel?

Yess!! You can achieve this by using ...READ MORE

answered Sep 26, 2018 in Power BI by Kalgi
• 35,640 points
30 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,100 points

edited Oct 11, 2018 by Kalgi 81 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,640 points
41 views
0 votes
1 answer

Power BI Dax Multiple IF AND Statements

I suspect your issue is that alphabetically ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,640 points
3,505 views
0 votes
1 answer

Distinct count filtered by condition using Power BI Dax

Try this, it should work: DistinctCountActiveMonths = CALCULATE( ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 35,640 points
1,869 views
0 votes
1 answer

How many reports can be created in one Power BI reports

You have a limit of 1000 datasets ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,040 points
20 views
0 votes
1 answer

Is there a way to convert JSON data to readable table in power bi

Follow these steps step 1 - Click on ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,040 points
1,062 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.