title: Power BI - Date calculations for a non standard month
body: <p><em>Power BI Desktop<br />
  Version: 2.73.5586.984 64-bit (September 2019)</em></p>
  <p>I am creating a calculated column to determine if a ticket has been completed in a &quot;Current&quot; or &quot;Backlog&quot; state. Our reporting period month goes from the 26th of the month to the 25th. If a ticket was completed in the reporting period m/26 - m/25 it would be considered &quot;Current.&quot; If the ticket was completed outside of that time frame then it would be &quot;Backlog.&quot; Also, if the current ticket has not been completed but still has the possibility of being completed in the same reporting period then that would be listed as &quot;Current&quot;, but if it goes on to the next reporting month then it would be &quot;Backlog.&quot;</p>
  <blockquote>
  <p><em>Example:<br />
  Created 1/1/2021 &amp; Completed 1/10/2021 = Current <br />
  Created 1/1/2021 &amp; Completed 3/18/2021 = Backlog <br />
  Created 1/25/2021 &amp; Completed 1/26/2021 = Backlog <br />
  Created 4/20/2021 &amp; Not Completed &amp; Today [4/30/2021] = Backlog<br />
  Created 4/29/2021 &amp; Not Completed &amp; Today [4/30/2021] = Current</em></p>
  </blockquote>
  <p>I have written the following DAX to handle this but I seem to run into issues after the end/at the start of the reporting period, where the calculations don't work properly and everything lists as either current or backlog.</p>
  <p>I also have a helper column in my Date Table that determines what the current reporting period is based on the current day, but I am not using it in this formula, but can if it would make it more efficient.</p>
  <p>What is a better/proper way to do this?</p>
  <p>Current/Backlog Caluclated Column:</p>
  <pre><code>Current_Backlog = 
  
  VAR CreatedDay = Day(IR_SR[Created_Date])
  VAR CompletedDay = Day(IR_SR[Completed_Date])
  VAR CreatedMonth = Month(IR_SR[Created_Date])
  VAR CompletedMonth = Month(IR_SR[Completed_Date])
  VAR CreatedMonthAdd = Month(IR_SR[Created_Date])+1
  VAR CompletedMonthAdd = Month(IR_SR[Completed_Date])+1
  VAR CurrentMonth = Month(TODAY())
  VAR CurrentMonthAdd = Month(TODAY())+1
  VAR CurrentDay = Day(TODAY())
  
  RETURN
  //If the date the ticket was completed is before the 26th and the created and completed month match, mark as current
  IF(CompletedDay &lt; 26 &amp;&amp; CreatedMonth = CompletedMonth, &quot;Current&quot;,
      //If the completed date is after or equal to the 26th see if the created month plus one and completed month plus one match, mark as current
      IF(CreatedDay &gt;= 26 &amp;&amp; CompletedDay &gt;= 26 &amp;&amp; CreatedMonthAdd = CompletedMonthAdd, &quot;Current&quot;,
          //If the completed date is after or equal to the 26th and the created date is after or equal to the 26th see if the created and completed month plus one match, mark as current
          IF(CreatedDay &gt;= 26 &amp;&amp; CreatedMonthAdd = CompletedMonth, &quot;Current&quot;,
              //If the ticket is not completed and the created date is less then the 26th and the created month and current month match, mark as current
              IF(IR_SR[Open/Closed] = &quot;Open&quot; &amp;&amp; CurrentDay &lt; 26 &amp;&amp; CreatedDay &lt; 26 &amp;&amp; CreatedMonth = CurrentMonth, &quot;Current&quot;,
                  //If the ticket is not completed and the created date is greater then the 26th and the created month and current month match plus one, mark as current
                  IF(IR_SR[Open/Closed] = &quot;Open&quot; &amp;&amp; CurrentDay &gt;= 26 &amp;&amp; CurrentDay &lt; 1 &amp;&amp; CreatedDay &gt;= 26 &amp;&amp; CreatedMonthAdd = CurrentMonthAdd, &quot;Current&quot;,
                      IF(IR_SR[Open/Closed] = &quot;Open&quot; &amp;&amp; CurrentDay &lt; 26 &amp;&amp; CurrentDay &gt;= 1 &amp;&amp; CreatedDay &gt;= 26 &amp;&amp; CreatedMonthAdd = CurrentMonth, &quot;Current&quot;,
                      &quot;Backlog&quot;))))))
  </code></pre>
  <p>Current Reporting Month:</p>
  <pre><code>= Table.AddColumn(#&quot;Inserted Day Name&quot;, &quot;Reporting_Period&quot;, each if Date.Day([Date]) &gt;= 26
  then Date.StartOfMonth(Date.AddMonths([Date], 1))
  else Date.StartOfMonth([Date]))
  </code></pre>