cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dates!! Not quite sure how to describe this in one line ....but need help

Hi All

Am newish to Power Bi and Dax - all self taught at moment, although am working through videos and find these forums really helpful. 

 

I have a problem which I just cannot get my head round how to solve.....

 

I am working with school term dates.

Have a "helper" table which has start and end dates for each term and also a term report date (which is at some point during a term).  This also has a "Quarter" allocated to each term in appropriate order, ie Autumn, Spring, Summer.

Have set up a custom date table where I work out what week a date falls into within school year (term starts beginning September); and have defined a Term and Quarter calculated column based on lookup using helper table.  So far so good..

 

I am working with Attendance data which has a column with a statistical meaning of  present/absent for each day of each term.  I have Many to One relationship between Attendance and Date Table.

 

I need to be able to have a table visual which shows a term and count of absent, count of present, % Attendance for term selected from a slicer but also needs to show previous term's data.  

 

I hvae managed to create measures on Date table for:

  • selected term (from slicer)
  • selected quarter (used in order to allow me to identify previous quarter)
  • selected term start date / end date / report date
  • previous term
  • pervious quarter
  • previous term start date / end date / report date

All work perfectly, I am happy to say

 

I then use these measures in Attendance table to do a count of absent / present and calculate % Attendance

 

  • CountAbsent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent",1,0))) 
  • CountPresent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present",1,0)))
  • %PresentTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermEndDate]))

 

All work perfectly

 

Also done for the period up to selected term report date:

  • CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))
  • CountPresent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present" && Attendance[Mark date]<=[TermReportDate],1,0)))
  • %PresentTerm-Report = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermReportDate]))
Again these all work perfectly.
 
However I cannot get this to work for previous term (which would be whole of term), I know my measures are correct as I have tested by creating a table which shows me the selected term information (dates) as well as previous term information (dates)
 
I followed same principles for counting absent/previous as above, ie:
 
  • CountAbsent-Previous = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate],1,0)))  
  • %PresentPreviousTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate]))
But the answer I get is zero for the counts and absolutely nothing for the %.  Am baffled as to why this will not work when it works on the previous term information table.  See attached screenshot.
 
Any suggestions (in idiot speak) would be gratefully received!!!
 
Attendance.PNG
5 REPLIES 5
Super User II
Super User II

I think you should rewrite the measures that are in this form:

CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))

 

get rid of the calculate round the iterator, in fact get rid of the iterator and the assignment of 1 or 0,       just FILTER the table using the clauses you have and COUNTROWS.

 

In the measure that doesn't work, you will have to debug it to see which clause or condition is causing no rows to be returned.  You'll be in a better position if you rewrite according to the advice above.

Reduce the filter clauses until you start to get values returned, then add in another clause.  Test to see if the measures e.g. [PreviousTermStartDate] are populated.

 

Obviously I don't have your data model and data but I'll try and help you debug it if you get stuck.  Good luck

Many thanks @HotChilli will give it a go!

Hi @HotChilli 

 

Rewrote measure as follows:

 

CountAbsent-ReportFilter =
COUNTROWS (
CALCULATETABLE (
Attendance,
FILTER (
Attendance,
Attendance[Statistical meaning] ="Absent"
),
FILTER (
Attendance,
Attendance[Mark date]>= [TermStartDate]
),
FILTER (
Attendance,
Attendance[Mark date]<= [TermReportDate]
)
)
)
 
This works correctly as is still looking at the term that is selected on the slicer
 
When I tried this one though
 
CountAbsent-Previous =
COUNTROWS (
CALCULATETABLE (
Attendance,
FILTER (
Attendance,
Attendance[Statistical meaning] ="Absent"
),
FILTER (
Attendance,
Attendance[Mark date]>= [PreviousTermStartDate]
),
FILTER (
Attendance,
Attendance[Mark date]<= [PreviousTermEndDate]
)
)
)
 
I get nothing at all (previously I did get zero with the measure I wrote (see original post)).  
 
I am thinking that the slicer on Term (from date table) is filtering the attendance table so that I actually don't have any rows with the dates I want in it.  I am not sure how to get around this as I need the slicer to work on the table visual as I want to show data for the selected term but need to also show previous term data on same row of table visual.
 
Here is the data for whole terms:
 
TermCountAbsentCountPresent%PresentYTDTermStartDateTermEndDate
Autumn443785394.66%02/09/201907/01/2020
Spring469577792.49%08/01/202019/04/2020
Summer43630199.32%20/04/202031/08/2020

 

Here is data up to report date in each term

 

TermCountAbsent-ReportCountPresent-Report%PresentTerm-ReportTermStartDateTermReportDate
Autumn321638995.22%02/09/201929/11/2019
Spring292410093.35%08/01/202004/03/2020
Summer32472699.33%20/04/202019/06/2020

 

All terms and dates come from Date table and attendance data from Attendance table with many to one relationship based on date field.

 

I have a slicer based on Term.

 

If I do a table visual to pull out date information on currently selected term and previously selected term (measures in original post), it works correctly:

 

QuarterTermTermStartDateTermEndDateTermReportDatePreviousQuarterPreviousTermPreviousTermStartDatePreviousTermEndDate
4Summer20/04/202031/08/202019/06/20203Spring08/01/202019/04/2020

 

If I have a table visual to pull out attendance data using measures outlined above (with changes you suggested), I get this:

 

TermCountAbsentCountPresent%PresentTermCountAbsent-ReportCountAbsent-ReportFilterCountPresent-Report%PresentTerm-ReportCountAbsent-Previous
Summer43630199.32%3232472699.33% 

 

 I want it to put the number of absent from the whole of Spring Term (in first table above) - so figure for CountAbsent-Previous should be 469.

 

Happy to share file but not sure of best way to do that...

Post your pbix on a 3rd party site that you trust e.g. box and i'll try and look at it this weekend.

 

On the re-written measures - they are still overly complex.  You can combine the filter clauses rather than sending 3 versions of the filtered table as table filters.

I have literally just got to the solution with help from previous suggestion from @amitchandak that I have finally got my head around.  Is still a bit of a fudge but I can live with it...

 

In Power Query have created conditional column to give each row that has Absent a value of 1 (will do likewise for present).  

 

In my data table I have a quarter assigned to each term in appropriate order.

 

Managed to now get an answer with a measure using:

 

CountAbsent-Previous = CALCULATE(sum(Attendance[AbsentValue]), FILTER(ALL('Academic Date Table'), 'Academic Date Table'[Quarter]=max('Academic Date Table'[Quarter])-1))
 
Thanks so much for your offer of help @HotChilli 

Am gradually getting my head around things (sure I will be back though!!).

This has significantly reduced the work in my previous incarnation which had a lot of "bodges" and extra queries as well as wide tables to get to the same answers!!!!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.