Forum Discussion

cmaloyb's avatar
cmaloyb
Icon for Helper II rankHelper II
6 years ago
Solved

Line Graph with Multiple Months and Averages

Hi there,   I am fairly new to Power BI and need help with creating a trend line/line graph from my data.   My first table, Monthly Turn-In Table tracks when students A through G turn in a monthl...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi cmaloyb ,

    Please try to update the formula of measure as below:

    NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)])
VAR b=DIVIDE(CALCULATE(DISTINCTCOUNT('Table 1'[Student ID])),
COUNTBLANK('Table 2'[Program Start Date])+CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)),0)
return b

    Here updated the formula of calculating the number of active students:

    1. Calculate the number of program start date which is blank     

    COUNTBLANK('Table 2'[Program Start Date])

    2. Calculate the number of program start data which is less than assignment submit date

    CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)

    3. Add both to get active student numbers

    Best Regards

    Rena

cmaloyb's avatar
cmaloyb
Icon for Helper II rankHelper II
6 years ago

Anonymous ,

 

Thank you very much, that seems to work. I did find one issue through extending the example. When the year ends (2019 in this case) and the students begin turning in their monthly assignments the following year (2020), those students with Program Start Dates in 2019 (S0003, S0005, S0007) are no longer counted as active students. 

 

Would you mind explaining what dax in the measure means to help me work this out?

 

Connor

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi cmaloyb ,

Where did the dates you put on line chart Axis come from? Is it from a calendar table? Or from the field Turn-in Date (Month) in Table 1? 

Best Regards

Rena

  • cmaloyb's avatar
    cmaloyb
    Icon for Helper II rankHelper II
    6 years ago

    Hi Anonymous, 

     

    The dates on the Axis came from the field Turn-In Date (Month) in Table 1.

     

    R/

    Connor

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi cmaloyb ,

      Please update the formula of measure as below:

      NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)])
      VAR b=CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))/
      CALCULATE(DISTINCTCOUNT('Table 2'[Student ID]),FILTER('Table 2',ISBLANK('Table 2'[Program Start Date])|| (YEAR('Table 2'[Program Start Date])<=YEAR(A)&&MONTH('Table 2'[Program Start Date])<=MONTH(a))))
      return b

      And the meaning of the formula as follow:

      1. Get the current Turn-in Date    

      VAR a=MAX('Table 1'[Turn-in Date (Month)])

      2. Count the number of the submission

      CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))

      3. Count the number of the active student

        1) Get the records which the program start date is blank or less than assignment turn in date 

      FILTER('Table 2',ISBLANK('Table 2'[Program Start Date])|| (YEAR('Table 2'[Program Start Date])<=YEAR(A)&&MONTH('Table 2'[Program Start Date])<=MONTH(a)))

         2) Count of the number of the students who satisfy the above conditions

      DISTINCTCOUNT('Table 2'[Student ID])

      In addition, please find the details of the functions used in formula in following documentations:

      https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

      https://docs.microsoft.com/en-us/dax/filter-function-dax

      Best Regards

      Rena

      • cmaloyb's avatar
        cmaloyb
        Icon for Helper II rankHelper II
        6 years ago

        Hi Anonymous,

         

        So I added I changed a Program Start Date in Table 2 to test the measure, however, it still does not include those dates that were passed in the previous year. 

         

        New Table 2:

         

        Student IDProgram Start Date
        S0001 
        S0002 
        S00033/1/2019
        S0004 
        S00054/1/2019
        S00061/2/2020
        S0007

        10/1/2019

         

        I added more submissions onto Table 1 as well for January 2020 to test the measure.

         

        T0061BS00021/3/2020
        T0062CS00031/18/2020
        T0063DS00041/19/2020
        T00064GS00071/20/2020

         

        With the measure NumberofsubA put into a Line Graph by Month, January 2020 has NumberofsubA = 1.00

         

        Given the 4 submissions in January 2020 with the 3 blank Program Start Dates & 1  Jan. 2020 Program Start Date (4 Program Start Dates), this would be: 4 Submissions/4 Program Start Dates = 1.00. 

         

        We are still missing those 3 Program Start Dates from the previous year in 2019 (S0003, S0005 & S0007).

         

        I have tried modifying the given measure, however I'm usually left with an error or a calculation that is very far from correct.

         

        Any ideas? 

         

        I would like to thank you so much for your help so far! It has been wonderful learning!

         

        R/

        Connor