Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

Hi all, Appreciate if you're able to assist my following question. I'm having two matrices as A and B. I formed them by using the raw data.   Now, I'm having an idea to take the consolidated data...
Need Help
Show and Tell
  • bolfri's avatar
    bolfri
    3 years ago

    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:

    ProgramStatus Start DateStatus End Date
    A2022-01-012022-02-01
    A2022-01-052022-01-20
    A2022-01-102022-02-01
    A2022-01-152022-01-25
    A2022-01-202022-02-02
    A2022-01-252022-01-20
    B2022-01-012022-02-01
    B2022-01-052022-01-25
    B2022-01-102022-02-01
    B2022-01-152022-01-20
    B2022-01-202022-02-02
    B2022-01-252022-01-25
    C2022-01-032022-01-02
    C2022-01-082022-01-20
    C2022-01-132022-02-01
    C2022-01-182022-01-25
    C2022-01-232022-02-01
    C2022-01-282022-01-20

     

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

     

     
bolfri's avatar
bolfri
Solution Sage
3 years ago

Without sample data it's hard to write down a DAX for you to use it, but I've done this in different topic.

 

PRE

1. Real end date need to be a date

2. Completion end date need to be a date

 

CALENDAR

3. Create dim_calendar table to hold all the dates between min and max possible date

4. Connect dim_calendar by date to Real end date and set it inactive

5. Connect dim_calendar by date to Completion end date and set it inactive

 

MEASURE

6. Create "sum of operation time by real end" measure including USERELATIONSHIP() function in it between the Dates in dim_calendar and Real end date

7. Create "sum of operation time by completion" measure including USERELATIONSHIP() function in it between the Dates in dim_calendar and Completion end date

8. Create "difference" measure that is simply measure 1 - measure 2

 

Viz it using a Date from dim_calendar table.

 

If you share a sample data (only few rows needed), I can help you with that.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

bolfri selimovd 

Hey guys, sorry for late reply. Wishing you & your family a belated Merry Christmas!

 

I come out with a datasheet as per attached link and stated the challenges and my expectation on the tab of "18 March 2022". Hope that you're able to solve my doubts.

 

Thanks!

 

https://we.tl/t-ZekbLXTvoz 

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    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:

    ProgramStatus Start DateStatus End Date
    A2022-01-012022-02-01
    A2022-01-052022-01-20
    A2022-01-102022-02-01
    A2022-01-152022-01-25
    A2022-01-202022-02-02
    A2022-01-252022-01-20
    B2022-01-012022-02-01
    B2022-01-052022-01-25
    B2022-01-102022-02-01
    B2022-01-152022-01-20
    B2022-01-202022-02-02
    B2022-01-252022-01-25
    C2022-01-032022-01-02
    C2022-01-082022-01-20
    C2022-01-132022-02-01
    C2022-01-182022-01-25
    C2022-01-232022-02-01
    C2022-01-282022-01-20

     

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