Forum Discussion
Helper IILine Graph with Multiple Months and Averages
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 bHere 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
Helper IIHi Anonymous,
The dates on the Axis came from the field Turn-In Date (Month) in Table 1.
R/
Connor
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
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 bHere 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
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 ID Program Start Date S0001 S0002 S0003 3/1/2019 S0004 S0005 4/1/2019 S0006 1/2/2020 S0007 10/1/2019
I added more submissions onto Table 1 as well for January 2020 to test the measure.
T0061 B S0002 1/3/2020 T0062 C S0003 1/18/2020 T0063 D S0004 1/19/2020 T00064 G S0007 1/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
- cmaloyb
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 ID Program Start Date S0001 S0002 S0003 3/1/2019 S0004 S0005 4/1/2019 S0006 1/2/2020 S0007 10/1/2019
I added more submissions onto Table 1 as well for January 2020 to test the measure.
T0061 B S0002 1/3/2020 T0062 C S0003 1/18/2020 T0063 D S0004 1/19/2020 T00064 G S0007 1/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
- cmaloyb
Anonymous that seems to work!
Thank you so much for your help!
R/
Connor
- cmaloyb
Anonymous ,
I've run into a requirement change where those who were submitting their monthly assignments will not count in the numerator until their program start date is met.
How do I include this filter in the numerator? I tried using the filtering method used in the denominator, however it does not return the correct results.
R/
Connor