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,870 points
275 views

1 answer to this question.

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,080 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
• 37,320 points
48 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
• 37,320 points
39 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
• 37,320 points
26 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
• 37,320 points
46 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 111 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
• 37,320 points
53 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22 in Power BI by VNK
5,551 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
• 37,320 points
3,737 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,080 points
37 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,080 points
1,616 views