Forum Discussion

rexeubank's avatar
rexeubank
Frequent Visitor
6 years ago
Solved

Sales Data Pre/Post a Specific Date

Hello,   I'm having trouble with a calculated column. I am trying to see sales data 3 weeks prior and post-visitation to a store so I can compare with the week that the store is visited.   I have...
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi rexeubank 

     

    The claculated column can't be changed by the other table's filter. I believe you can achieve this by changing it to the measure as below:

    Measure = SUMX(
        FILTER(
            ALLSELECTED('Sales'),
            AND(
                'Sales'[Week Number] = MAX('Combined Recaps'[WM Week]),
                'Sales'[Store Number] = MAX('Combined Recaps'[Retail ID])
            )
        ),
        'Sales'[Sales]
    )

rexeubank's avatar
rexeubank
Frequent Visitor
6 years ago

I apologize for the vagueness. Maybe this can help:

 

Below is my data.

On the top, I have the visitation export (what stores were visited and when). This is where the calculated columns are located.

On the bottom is the sales data. I match the week numbers and the store numbers (Retail ID/Store Number) and sum the sales data based on those two criteria.

And the calculated column code for easier viewing.

I need to get the weekly sales into a format where I can filter them by the Brand & Type from the Sales tab.

 

Current Week =
   SUMX(
        FILTER(
            'Sales',
            AND(
                'Sales'[Week Number] = 'Combined Recaps'[WM Week],
                'Sales'[Store Number] = 'Combined Recaps'[Retail ID]
            )
        ),
        'Sales'[Sales]
    )

 

v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi rexeubank 

 

The claculated column can't be changed by the other table's filter. I believe you can achieve this by changing it to the measure as below:

Measure = SUMX(
        FILTER(
            ALLSELECTED('Sales'),
            AND(
                'Sales'[Week Number] = MAX('Combined Recaps'[WM Week]),
                'Sales'[Store Number] = MAX('Combined Recaps'[Retail ID])
            )
        ),
        'Sales'[Sales]
    )