Creating a new date table

[abhinav]

I am trying to create a new table where each column is the last date of a month and the values can be derived using values from other tables. When I try to create a table with dates, the dates appear in row instead of columns. How can I make a new table where each column is last date of the month?

[v-zhangti]

Hi, @abhinav 

You can use the EOMONTH function to get the date of the last day of each month. What kind of output do you want?

It is better if you can provide a simplified PBIX file. Please exclude the sensitive information before share these information. Thank you.

Best Regards,

Charlotte Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please share an example of what your desired output table would look like, and what inputs you would use.  Also, why do you want a table structured that way?  It may not be the best model structure for analysis/visualization.

Pat

[abhinav]

I have an excel file with a table which has some data for different fields with amount, start date, end date, etc.

Input Table:

input

I want to create two tables in power bi which are created using normal distribution on amount for every month between start date and end date for every row.

Output Table 1:

output1

Distribution Formula 1:

formula1

Output Table 2:

output2

Distribution Formula 2:

formula2

The output table cannot be created beforehand in excel. Only the input table is available and I want to create the output tables in PowerBI.

How can I create something like the output tables attached or is there a better way to do this?

[speedramps]

Please consider this solution and click the thumbs up and accept solution. 

In Edit query add a new column with
= Date.AddMonths( [previousdate] , -1 )