cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum sales by selected time periods where column has same value in both

Hi Experts,

I'm trying to do a dynamic sum based on time periods selected to compare changes over the time period. The problem I'm having is that I need to be able to only sum sales when a store is present in both the pre and post time periods.

My data is structured as follows:

 Store Item Sales Time Period 1 Item A 243 Q2 2023 1 Item B 200 Q2 2023 1 Item C 195 Q2 2023 2 Item A 377 Q2 2023 2 Item B 158 Q2 2023 2 Item C 235 Q2 2023 2 Item D 374 Q2 2023 3 Item A 245 Q2 2023 3 Item B 116 Q2 2023 3 Item C 392 Q2 2023 2 Item A 287 Q1 2023 2 Item B 188 Q1 2023 2 Item C 114 Q1 2023 2 Item D 398 Q1 2023 3 Item A 148 Q1 2023 3 Item B 325 Q1 2023 3 Item C 214 Q1 2023 4 Item A 348 Q1 2023 4 Item B 298 Q1 2023

My tables are setup as follows:

Formulas for Post Period Sales and Pre Period Sales are as follows:

``````Post Period Sales =
CALCULATE(
SUM(FactSales[Sales]),
GROUPBY(DimItem, DimItem[Item])
)

Pre Period Sales =
CALCULATE(
SUM(FactSales[Sales]),
REMOVEFILTERS(DimTimePeriod[Time Period]),
GROUPBY(DimItem, DimItem[Item]),
USERELATIONSHIP(FactSales[Time Period], DimTimePeriodCompare[Time Period])
)``````

Layout on canvas for simple table would have the DimTimePeriod and DimTimePeriodComparison in slicers so the end user can select which time periods they want to compare.

How can I adjust my formulas to omit any stores that do not appear in both time periods? The above example currently shows sum of sales for all stores, but Stores 1 and 4 are not in both time periods.

1 ACCEPTED SOLUTION
Frequent Visitor

For anyone that comes across this post, I was able to work out how to do it using an INTERSECT function and some clever filtering.

``````Pre Period Sales =
VAR PreStores =
CALCULATETABLE (
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
USERELATIONSHIP ( FactSales[Time Period], DimTimePeriodCompare[Time Period] ),
FILTERS(DimTimePeriodCompare[Time Period])
)
VAR PostStores =
CALCULATETABLE(
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
FILTERS(DimTimePeriod[Time Period]))
VAR CommonStores =
INTERSECT ( PreStores, PostStores )
RETURN
CALCULATE(
SUM(FactSales[Sales]),
REMOVEFILTERS(DimTimePeriod[Time Period]),
GROUPBY(DimItem, DimItem[Item]),
USERELATIONSHIP(FactSales[Time Period], DimTimePeriodCompare[Time Period]),
INTERSECT(CommonStores, VALUES(DimStore[Store])
))``````

``````Post Period Sales =
VAR PreStores =
CALCULATETABLE (
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
USERELATIONSHIP ( FactSales[Time Period], DimTimePeriodCompare[Time Period] ),
FILTERS(DimTimePeriodCompare[Time Period])
)
VAR PostStores =
CALCULATETABLE(
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
FILTERS(DimTimePeriod[Time Period]))
VAR CommonStores =
INTERSECT ( PreStores, PostStores )
RETURN
CALCULATE(
SUM( FactSales[Sales] ),
GROUPBY( DimItem, DimItem[Item] ),
INTERSECT(CommonStores, VALUES(DimStore[Store])))``````

2 REPLIES 2
Frequent Visitor

For anyone that comes across this post, I was able to work out how to do it using an INTERSECT function and some clever filtering.

``````Pre Period Sales =
VAR PreStores =
CALCULATETABLE (
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
USERELATIONSHIP ( FactSales[Time Period], DimTimePeriodCompare[Time Period] ),
FILTERS(DimTimePeriodCompare[Time Period])
)
VAR PostStores =
CALCULATETABLE(
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
FILTERS(DimTimePeriod[Time Period]))
VAR CommonStores =
INTERSECT ( PreStores, PostStores )
RETURN
CALCULATE(
SUM(FactSales[Sales]),
REMOVEFILTERS(DimTimePeriod[Time Period]),
GROUPBY(DimItem, DimItem[Item]),
USERELATIONSHIP(FactSales[Time Period], DimTimePeriodCompare[Time Period]),
INTERSECT(CommonStores, VALUES(DimStore[Store])
))``````

``````Post Period Sales =
VAR PreStores =
CALCULATETABLE (
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
USERELATIONSHIP ( FactSales[Time Period], DimTimePeriodCompare[Time Period] ),
FILTERS(DimTimePeriodCompare[Time Period])
)
VAR PostStores =
CALCULATETABLE(
VALUES ( FactSales[Store] ),
ALLCROSSFILTERED(FactSales),
FILTERS(DimTimePeriod[Time Period]))
VAR CommonStores =
INTERSECT ( PreStores, PostStores )
RETURN
CALCULATE(
SUM( FactSales[Sales] ),
GROUPBY( DimItem, DimItem[Item] ),
INTERSECT(CommonStores, VALUES(DimStore[Store])))``````

Super User

For Post Period Sales, you can try and see if this works...

``````Post Period Sales =
var _stores = CALCULATETABLE( VALUES(FactSales[Store]),
REMOVEFILTERS(DimTimePeriod[Time Period]),
USERELATIONSHIP(FactSales[Time Period], DimTimePeriodCompare[Time Period]),
FactSales[Time Period] in VALUES(DimTimePeriodCompare[Time Period]))

RETURN CALCULATE( SUM(FactSales[Sales]), _stores)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors