Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
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" )
)
Great, this was exactly what I needed, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |