topic Re: Dates!! Not quite sure how to describe this in one line ....but need help in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1464401#M27691
<P>I think you should rewrite the measures that are in this form:</P>
<P><SPAN>CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))</SPAN></P>
<P> </P>
<P><SPAN>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.</SPAN></P>
<P> </P>
<P><SPAN>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.</SPAN></P>
<P><SPAN>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.</SPAN></P>
<P> </P>
<P><SPAN>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</SPAN></P>Thu, 29 Oct 2020 23:14:09 GMTHotChilli2020-10-29T23:14:09ZDates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1464008#M27679
<P>Hi All</P><P>Am newish to Power Bi and Dax - all self taught at moment, although am working through videos and find these forums really helpful. </P><P> </P><P>I have a problem which I just cannot get my head round how to solve.....</P><P> </P><P>I am working with school term dates.</P><P>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.</P><P>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..</P><P> </P><P>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.</P><P> </P><P>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. </P><P> </P><P>I hvae managed to create measures on Date table for:</P><UL><LI>selected term (from slicer)</LI><LI>selected quarter (used in order to allow me to identify previous quarter)</LI><LI>selected term start date / end date / report date</LI><LI>previous term</LI><LI>pervious quarter</LI><LI>previous term start date / end date / report date</LI></UL><P>All work perfectly, I am happy to say</P><P> </P><P>I then use these measures in Attendance table to do a count of absent / present and calculate % Attendance</P><P> </P><DIV><UL><LI><SPAN>CountAbsent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent",1,0))) </SPAN></LI></UL><DIV><DIV><UL><LI><SPAN>CountPresent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present",1,0)))</SPAN></LI></UL></DIV></DIV></DIV><DIV><UL><LI><SPAN>%PresentTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermEndDate]))</SPAN></LI></UL></DIV><P> </P><P>All work perfectly</P><P> </P><P>Also done for the period up to selected term report date:</P><DIV><UL><LI><SPAN>CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))</SPAN></LI></UL></DIV><DIV><UL><LI><SPAN>CountPresent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present" && Attendance[Mark date]<=[TermReportDate],1,0)))</SPAN></LI></UL><DIV><DIV><UL><LI><SPAN>%PresentTerm-Report = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermReportDate]))</SPAN></LI></UL><DIV><SPAN>Again these all work perfectly.</SPAN></DIV><DIV> </DIV><DIV><SPAN>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)</SPAN></DIV><DIV> </DIV><DIV><SPAN>I followed same principles for counting absent/previous as above, ie:</SPAN></DIV><DIV> </DIV><DIV><DIV><UL><LI><SPAN>CountAbsent-Previous = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate],1,0))) </SPAN></LI></UL><DIV><DIV><UL><LI><SPAN>%PresentPreviousTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate]))</SPAN></LI></UL></DIV></DIV><DIV><SPAN>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.</SPAN></DIV><DIV> </DIV><DIV><SPAN>Any suggestions (in idiot speak) would be gratefully received!!!</SPAN></DIV><DIV> </DIV><DIV><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Attendance.PNG" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/386299iEA85E60AE0513382/image-size/large?v=1.0&px=999" role="button" title="Attendance.PNG" alt="Attendance.PNG" /></span></SPAN></DIV></DIV></DIV></DIV></DIV></DIV>Thu, 29 Oct 2020 17:21:22 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1464008#M27679BLAWHEEM12020-10-29T17:21:22ZRe: Dates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1464401#M27691
<P>I think you should rewrite the measures that are in this form:</P>
<P><SPAN>CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0)))</SPAN></P>
<P> </P>
<P><SPAN>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.</SPAN></P>
<P> </P>
<P><SPAN>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.</SPAN></P>
<P><SPAN>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.</SPAN></P>
<P> </P>
<P><SPAN>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</SPAN></P>Thu, 29 Oct 2020 23:14:09 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1464401#M27691HotChilli2020-10-29T23:14:09ZRe: Dates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1465204#M27710
<P>Many thanks <LI-USER uid="81111"></LI-USER> will give it a go!</P>Fri, 30 Oct 2020 07:46:03 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1465204#M27710BLAWHEEM12020-10-30T07:46:03ZRe: Dates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1465275#M27711
<P>Hi <LI-USER uid="81111"></LI-USER> </P><P> </P><P>Rewrote measure as follows:</P><P> </P><DIV><DIV><SPAN>CountAbsent-ReportFilter = </SPAN></DIV><DIV><SPAN>COUNTROWS (</SPAN></DIV><DIV><SPAN>CALCULATETABLE (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Statistical meaning] ="Absent"</SPAN></DIV><DIV><SPAN>),</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Mark date]>= [TermStartDate]</SPAN></DIV><DIV><SPAN>),</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Mark date]<= [TermReportDate]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV> </DIV><DIV><SPAN>This works correctly as is still looking at the term that is selected on the slicer</SPAN></DIV><DIV> </DIV><DIV><SPAN>When I tried this one though</SPAN></DIV><DIV> </DIV><DIV><DIV><SPAN>CountAbsent-Previous = </SPAN></DIV><DIV><SPAN>COUNTROWS (</SPAN></DIV><DIV><SPAN>CALCULATETABLE (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Statistical meaning] ="Absent"</SPAN></DIV><DIV><SPAN>),</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Mark date]>= [PreviousTermStartDate]</SPAN></DIV><DIV><SPAN>),</SPAN></DIV><DIV><SPAN>FILTER (</SPAN></DIV><DIV><SPAN>Attendance,</SPAN></DIV><DIV><SPAN>Attendance[Mark date]<= [PreviousTermEndDate]</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV> </DIV><DIV><SPAN>I get nothing at all (previously I did get zero with the measure I wrote (see original post)). </SPAN></DIV><DIV> </DIV><DIV><SPAN>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.</SPAN></DIV><DIV> </DIV><DIV><SPAN>Here is the data for whole terms:</SPAN></DIV><DIV> </DIV><DIV><TABLE><TBODY><TR><TD>Term</TD><TD>CountAbsent</TD><TD>CountPresent</TD><TD>%PresentYTD</TD><TD>TermStartDate</TD><TD>TermEndDate</TD></TR><TR><TD>Autumn</TD><TD>443</TD><TD>7853</TD><TD>94.66%</TD><TD>02/09/2019</TD><TD>07/01/2020</TD></TR><TR><TD>Spring</TD><TD>469</TD><TD>5777</TD><TD>92.49%</TD><TD>08/01/2020</TD><TD>19/04/2020</TD></TR><TR><TD>Summer</TD><TD>43</TD><TD>6301</TD><TD>99.32%</TD><TD>20/04/2020</TD><TD>31/08/2020</TD></TR></TBODY></TABLE><P> </P><P>Here is data up to report date in each term</P><P> </P><TABLE><TBODY><TR><TD>Term</TD><TD>CountAbsent-Report</TD><TD>CountPresent-Report</TD><TD>%PresentTerm-Report</TD><TD>TermStartDate</TD><TD>TermReportDate</TD></TR><TR><TD>Autumn</TD><TD>321</TD><TD>6389</TD><TD>95.22%</TD><TD>02/09/2019</TD><TD>29/11/2019</TD></TR><TR><TD>Spring</TD><TD>292</TD><TD>4100</TD><TD>93.35%</TD><TD>08/01/2020</TD><TD>04/03/2020</TD></TR><TR><TD>Summer</TD><TD>32</TD><TD>4726</TD><TD>99.33%</TD><TD>20/04/2020</TD><TD>19/06/2020</TD></TR></TBODY></TABLE><P> </P><P>All terms and dates come from Date table and attendance data from Attendance table with many to one relationship based on date field.</P><P> </P><P>I have a slicer based on Term.</P><P> </P><P>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:</P><P> </P><TABLE><TBODY><TR><TD>Quarter</TD><TD>Term</TD><TD>TermStartDate</TD><TD>TermEndDate</TD><TD>TermReportDate</TD><TD>PreviousQuarter</TD><TD>PreviousTerm</TD><TD>PreviousTermStartDate</TD><TD>PreviousTermEndDate</TD></TR><TR><TD>4</TD><TD>Summer</TD><TD>20/04/2020</TD><TD>31/08/2020</TD><TD>19/06/2020</TD><TD>3</TD><TD>Spring</TD><TD>08/01/2020</TD><TD>19/04/2020</TD></TR></TBODY></TABLE><P> </P><P>If I have a table visual to pull out attendance data using measures outlined above (with changes you suggested), I get this:</P><P> </P><TABLE><TBODY><TR><TD>Term</TD><TD>CountAbsent</TD><TD>CountPresent</TD><TD>%PresentTerm</TD><TD>CountAbsent-Report</TD><TD>CountAbsent-ReportFilter</TD><TD>CountPresent-Report</TD><TD>%PresentTerm-Report</TD><TD>CountAbsent-Previous</TD></TR><TR><TD>Summer</TD><TD>43</TD><TD>6301</TD><TD>99.32%</TD><TD>32</TD><TD>32</TD><TD>4726</TD><TD>99.33%</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P> 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.</P><P> </P><P>Happy to share file but not sure of best way to do that...</P></DIV></DIV></DIV>Fri, 30 Oct 2020 08:26:35 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1465275#M27711BLAWHEEM12020-10-30T08:26:35ZRe: Dates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1466215#M27736
<P>Post your pbix on a 3rd party site that you trust e.g. box and i'll try and look at it this weekend.</P>
<P> </P>
<P>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.</P>Fri, 30 Oct 2020 15:06:27 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1466215#M27736HotChilli2020-10-30T15:06:27ZRe: Dates!! Not quite sure how to describe this in one line ....but need help
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1466224#M27737
<P>I have literally just got to the solution with help from previous suggestion from <LI-USER uid="148838"></LI-USER> that I have finally got my head around. Is still a bit of a fudge but I can live with it...</P><P> </P><P>In Power Query have created conditional column to give each row that has Absent a value of 1 (will do likewise for present). </P><P> </P><P>In my data table I have a quarter assigned to each term in appropriate order.</P><P> </P><P>Managed to now get an answer with a measure using:</P><P> </P><DIV><DIV><DIV><SPAN>CountAbsent-Previous = CALCULATE(sum(Attendance[AbsentValue]), FILTER(ALL('Academic Date Table'), 'Academic Date Table'[Quarter]=max('Academic Date Table'[Quarter])-1))</SPAN></DIV><DIV> </DIV><DIV><SPAN>Thanks so much for your offer of help <LI-USER uid="81111"></LI-USER> <BR /><BR />Am gradually getting my head around things (sure I will be back though!!).<BR /><BR />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!!!!</SPAN></DIV></DIV></DIV>Fri, 30 Oct 2020 15:12:09 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-one-line-but-need/m-p/1466224#M27737BLAWHEEM12020-10-30T15:12:09Z