Column values with dynamic current period and comparison period

[Mainer04401]

I have stores on price zones that may change from one month to the next.  In a table view, I want to view groups of stores by their current price zone and a comparison period price zone.  The current and comparison period would be selected by the user.  

For example ...

Comparison Period Price Zone	Current Period Price Zone	Current Period Sales	Comparison Period Sales
A	A
B	B
A	B
B	A

The data is grouped by months, so I have a month table that filters the fact table.  In a report, there is a slicer for the month that the user can choose as their current period.  For the comparison period, I duplicated the month table but did not setup any relationships, and added another slicer for the user to choose the comparison period.  

I've been able to get the "Comparison Period Sales" to work properly in this measure:

Sales (Comparison Period) =

var _comparisonperiod = CALCULATETABLE(

    VALUES(Sales_Calendar),

    REMOVEFILTERS(Sales_Calendar[Month of Year]),

    USERELATIONSHIP(Sales_Calendar[Month of Year], 'Calendar Comparison'[Month of Year]) )

    RETURN

    calculate([Sales], _comparisonperiod)

However, I can't figure out how to show the price zones of the comparison period.  I thought FIRSTNONBLANKVALUE would work in the foruma above but it doesn't work.  

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[Mainer04401 · Accepted solution]

I found a solution and I'll post it here in case anyone finds this in a search.

I continued to use the measure that I referenced before for calculating comparison values based on a secondary comparison calendar.  My solve for also being able to display attributes in that comparison time period was to use another table that had a column of unique store numbers and columns that listed the attribute for each month, so it looked like this:

Store #	January	February	March
1	Price Zone A	Price Zone A	Price Zone B
2	Price Zone A	Price Zone C	Price Zone C
3	Price Zone A	Price Zone A	Price Zone A

I created a relationship between the store numbers in this new table and my fact table.  Then I created two field parameters, both identical.  These parameters create a virtual table, with a column that lists each month.  I then created a relationship between that column and the primary calendar, and a relationship between the column in the duplicate parameter to the comparison parameter.  That column's values were formatted as 'TableName'[Month] so in the calendar tables I created custom columns with a concatenation of 'TableName' and the month in order to be able to link them.

Now, when a user selects a different comparison time period, the measures will reflect that comparison period selected and the attribute column (Price Zone) will also update accordingly.  Probably an unorthadox solution but it seems to work perfectly.  

[v-xiandat-msft]

Hi @Mainer04401,

Since isn't working as expected, might offer a more direct method to retrieve the price zone for a specific period, assuming you have a unique identifier for each store and period.

The following DAX might work for you:

Price Zone (Comparison Period) = 
VAR _comparisonPeriod = SELECTEDVALUE('Calendar Comparison'[Month of Year])
RETURN
CALCULATE(
    LOOKUPVALUE(
        'StorePrices'[Price Zone], 
        'StorePrices'[Store ID], SELECTEDVALUE('Stores'[Store ID]), 
        'StorePrices'[Month of Year], _comparisonPeriod
    ),
    ALL('Sales_Calendar')
)

You must create a  'Stores' table with unique store IDs

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.