Measure(s) for quarterly sales for a fact table with end and start date that crosses multiple Q's

[Anonymous]

Hi All,

I am trying to calculate quarterly sales through a measure. The fact table has a start date and an end date which crosses multiple quarters and sometimes multiple years. An example of the data is as follows:

I am using a separate date table with dates listed from 01-01-2019 to 31-12-2020 which includes a column for quarters of a year (Q1 2019, Q2 2019 etc). The start date of the fact table above is connected with the date columns of the date table.

The data spans across too many years to create (many) calculated columns in the fact tabel (a few columns would be ok though if really needed). Therefore a measure is needed in this case.

Which measure(s) would calculate quarterly sales in a table in the report view in PowerBI to get this result:

PS because of the confidentiality of the real data in PowerBI, the tables shown here have been made in Excel.

Thank you ver y much for your help.

[amitchandak · Accepted solution]

@Anonymous , I have disributed a similar kind of data and used that. See if the file attached after the signature can help you.

I have used only month in the calendar, you can use quarter

[Anonymous]

@amitchandak: can you please explain, why the 'Data'[id] column in there?

[Anonymous]

PS for other readers: I used the date table blelow and connected the [date] from this table to the date table from the solution given. Then I just dragged and dropped the "YearQuarter" in the table in the reports view which gives the desired result.

Date tabel =
ADDCOLUMNS (
CALENDAR (DATE(2019,1,1), DATE(2020,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"YearQuarter", "Q" & FORMAT ( [Date], "Q" ) & "/"& FORMAT ( [Date], "YYYY" )
)

[Anonymous]

Great, this was exactly what I needed, thank you!