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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello @cmaloyb,

You can create a measure as shown 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

line chart.JPG

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