Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Phillip_Nelson
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:

StoreItemSalesTime Period
1Item A243Q2 2023
1Item B200Q2 2023
1Item C195Q2 2023
2Item A377Q2 2023
2Item B158Q2 2023
2Item C235Q2 2023
2Item D374Q2 2023
3Item A245Q2 2023
3Item B116Q2 2023
3Item C392Q2 2023
2Item A287Q1 2023
2Item B188Q1 2023
2Item C114Q1 2023
2Item D398Q1 2023
3Item A148Q1 2023
3Item B325Q1 2023
3Item C214Q1 2023
4Item A348Q1 2023
4Item B298Q1 2023

 

My tables are setup as follows:

Phillip_Nelson_0-1685636062937.png

 

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. 

Phillip_Nelson_1-1685636237072.png

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
Phillip_Nelson
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])))

 

View solution in original post

2 REPLIES 2
Phillip_Nelson
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])))

 

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)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.