Calculating rows in a table for every Quarter in DAX, Power BI

0
votes

I have the following tbl_Episodes table (50K records):

      ID   Month   
      22   01/01/2019
      22   02/01/2019
      22   03/01/2019
      22   04/01/2019
      22   05/01/2019
      23   03/01/2020
      23   06/01/2020

I need to create a calculated column in DAX language, that will place "1" value on each row where it'll be the beginning or the end of the Quarter, otherwise - "0" value, as:

      ID   Month        NewColumn
      22   01/01/2019   1
      22   02/01/2019   0
      22   03/01/2019   1
      22   04/01/2019   0
      22   05/01/2019   0
      23   03/01/2020   1
      23   06/01/2020   1
1
powerbidaxcalculated-columns

1 Answers

1
votes

There are only 4 quarters, the simpler way is to switch dates : Add to your calendar table columns : (Consider that your calendar table has "Year" columns)

SWITCH([MONTH],date(1,1,[Year]),1,date(31,03,[Year]),1,
date(1,4,[Year]),1,date(30,6,[Year]),1 
,date(1,7,[Year],1,date(30,9,[Year]),1
,date(1,10,[Year]),1,date(31,12,[Year]),1,0)