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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.