cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors