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

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