cancel
Showing results for
Did you mean:
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!!!

5 REPLIES 5
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

Frequent Visitor

Many thanks @HotChilli will give it a go!

Frequent Visitor

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:

 Term CountAbsent CountPresent %PresentYTD TermStartDate TermEndDate Autumn 443 7853 94.66% 02/09/2019 07/01/2020 Spring 469 5777 92.49% 08/01/2020 19/04/2020 Summer 43 6301 99.32% 20/04/2020 31/08/2020

Here is data up to report date in each term

 Term CountAbsent-Report CountPresent-Report %PresentTerm-Report TermStartDate TermReportDate Autumn 321 6389 95.22% 02/09/2019 29/11/2019 Spring 292 4100 93.35% 08/01/2020 04/03/2020 Summer 32 4726 99.33% 20/04/2020 19/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:

 Quarter Term TermStartDate TermEndDate TermReportDate PreviousQuarter PreviousTerm PreviousTermStartDate PreviousTermEndDate 4 Summer 20/04/2020 31/08/2020 19/06/2020 3 Spring 08/01/2020 19/04/2020

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

 Term CountAbsent CountPresent %PresentTerm CountAbsent-Report CountAbsent-ReportFilter CountPresent-Report %PresentTerm-Report CountAbsent-Previous Summer 43 6301 99.32% 32 32 4726 99.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...

Super User II

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.

Frequent Visitor

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:

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!!!!

Announcements

#### Happy New Year from Power BI

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