Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Community,
I have an issue that I need your guidance on.
I have a fact table [f_Sales] with monthly sales of companies; [Company] - [Year.Month] - [Sales].
I also have a dimension table [d_Companies] for the companies: [Company] - [Company Subgroup] - [DateOfAcquisition] - [DateOfDivestment], where [DateOfAcquisition] represents the date the company was bought and [DateOfDivestment] represent the date the company was sold. If the company is not sold, the value is BLANK().
Finally, I have a date table [d_DateTable], but that is of lesser importance here.
In [f_Sales] table, I have sales data for all the companies. The time-range of the sales go both before [DateOfAcquisition] and, if the company is sold, also after [DateOfDivestment].
I have created a measure [Rolling12MonthSales] that returns the sum of the last twelve months of sales using DATESINPERIOD.
I have also created a second measure [AdjustedRolling12MonthSales] that returns 0 if the company is divested at the time:
RETURN AdjustedRolling12MonthSales
Now - here is my problem:
When I want to show [AdjustedRolling12MonthSales]in a matrix with dates in the columns and the rows with two levels [Company Subgroup] and [Company] - the AdjustedRolling12MonthSales will show correct monthly values for each [Company]. If the company has been sold, that specific column and row will show 0.
But the row subtotal [Company Subgroup] value does not sum up the values for AdjustedRolling12MonthSales , but instead returns regular [Rolling12MonthSales] values, ignoring that the underlying rows contain 0 values for some of the companies for the period.
How can I make it so that the row subtotal [Company Subgroup] value is indeed showing the subtotal value of the underlying row values?
Solved! Go to Solution.
Try this measure:
AdjustedRolling12MonthSales =
VAR AllCompanies =
SUMMARIZE (
f_Sales,
d_Companies[Company],
d_Companies[Company Subgroup],
d_Companies[DateOfDivestment],
d_DateTable[Date]
)
VAR DivestedCompanies =
FILTER (
AllCompanies,
NOT ISBLANK ( d_Companies[DateOfDivestment] )
&& d_DateTable[Date] > d_Companies[DateOfDivestment]
)
VAR AdjustedRolling12MonthSales =
SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN
AdjustedRolling12MonthSales
Proud to be a Super User!
Try this measure:
AdjustedRolling12MonthSales =
VAR AllCompanies =
SUMMARIZE (
f_Sales,
d_Companies[Company],
d_Companies[Company Subgroup],
d_Companies[DateOfDivestment],
d_DateTable[Date]
)
VAR DivestedCompanies =
FILTER (
AllCompanies,
NOT ISBLANK ( d_Companies[DateOfDivestment] )
&& d_DateTable[Date] > d_Companies[DateOfDivestment]
)
VAR AdjustedRolling12MonthSales =
SUMX ( DivestedCompanies, [Rolling12MonthSales] )
RETURN
AdjustedRolling12MonthSales
Proud to be a Super User!
Fantastic - you are a wizard, sir! Thank you!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |