Generate each trimester of the year

[Juju-PowerBI]

Hi,

I am looking to write a formula that will allow me to generate, for each date, all the trimester date, starting and ending date.

Is it possible ? 

Like the example below 

 

Thanks

 

[v-kelly-msft]

Hi  @Juju-PowerBI ,

You could realize it using dax formula:

Create a table as below:

Table 2 = ADDCOLUMNS( CALENDAR(DATE(YEAR(MIN('Table'[Date])),1,1),DATE(YEAR(MAX('Table'[Date])),12,31)),"Quarter","T"&QUARTER(''[Date]),"Year",YEAR(''[Date]))

Then create a relationship between column year:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Juju-PowerBI]

Hi @v-kelly-msft ,

Many thanks for the solution,

Do you know if it is also possible to do this in power query ?

Thanks you

Regard,

[v-kelly-msft · Accepted solution]

Hi @Juju-PowerBI ,

Yes,it can,but the process would be a little complicated,I would recommend using dax to realize it.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat]

This is doable in power query, but what do you plan to do with this table once generated?  You might want to consider making a simple Date table that has a Trimester column in it with T1, T2, T3, and making a relationship from the Date table to your data table via the Date column.  This will be simpler in terms of analysis and visualization than the table you've requested.  However, there may be a great use case for it.  Just checking.

Pat