How to Compare the Values in Two Different Visuals and Create a New Third Visual

Anonymous,

It's not a sample data. It's a real data that shows problem with understaning how Power BI works. Just by looking at number of columns that represent month. 🙂

1 Step. Get rid of all unnessesery columns in your data without losing any information that you need. This step includes deleting column with year, month, week etc for each date that you have. You can save 2 columns for rach date meaning eg start_date, start_datetime (if time is needed, if not leave only date).

This is the sample thata on which I will explain you your case:

Step 3. Create a calendar that holds all the dates in your model. You can put exact dates here or let Power BI create a table based on the dates you have in your model.

dim_calendar = CALENDAR(FIRSTDATE('Sample'[Status End Date]),LASTDATE('Sample'[Status End Date]))

 

Step 4. In dim_calendar table add new columns that representing year, month, quarter, week or what you need. In this scenario you'll have one month representation, not six-seven like in your case. It will be easier for you to not make mistakes in the future.

 

Year = YEAR([Date])

Month = MONTH([Date])

Week = WEEKNUM([Date])

 

Step 5. Create relationship between each dates to the calendar. Notice thtonly one of them is active and it's Status Start Date to Date.

 

Step 6. Create a measure that you need. In my case due to fact that I dont have any values I will be counting programs, but in your case it will be "sum of operation time".

Counting programs = COUNT('Sample'[Program])

 

Step 7. Create more measures. I will call them Table1Measure and Table2Measure.

 

Table 1 Measure calculates number of programs based on status start date.

Table1Measure =

CALCULATE(

    [Counting programs],

    USERELATIONSHIP(dim_calendar[Date],'Sample'[Status Start Date])

)

 

Table 2 Measure calculates number of program based on startus end date.

Table2Measure =

CALCULATE(

    [Counting programs],

    USERELATIONSHIP(dim_calendar[Date],'Sample'[Status End Date])

)

 

Step 8. Create another measure with KPI. This is what you've wanted to do in the first place.

KPI = DIVIDE([Table1Measure],[Table2Measure])

 

Step 9. Put the measures on the report. For Dates use only dim_calendar table eg weeks. In this scenario you can use multiple measures by one week fields and it will work perfectly. 🙂