Quarter over quarter inventory

[AEVMSP]

Hi everyone! I'm trying to create a measure to populate an inventory dashboard. I have regular inventory counts coming every month and have no issues with the data. Now, when we are crossing quarters (ie, going from Q1 to Q2) I need the counts of the last month of Q1 to be fixed and now report Q2's counts. In the example below, in Q2 if I filter as of May I would show Q1 = 450 and Q2 = 425, and as of June Q1 = 450 and Q2 = 500. I tried creating an expression and summarizing the table but didn't get close.

[daXtreme · Accepted solution]

Hi @AEVMSP 

In fact, this is very simple. Just add to your table a column that will store quarters. When you then put the quarters in your table and create this measure (below) you'll get what you want. For instance, put the quarters on the columns and have a slicer with dates (select a date on it and see what happens when you drop the measure on the canvas).

// To perform what you want you have to
// create a table called Quarters and 
// keep it disconnected. One of the columns
// will be Quarter (Q1, Q2, Q3, Q4), another
// will store the start date of the quarter
// and the third one will store the very last
// date of the quarter (QuarterStart, QuarterEnd).
// You can hide the last two columns as they are
// only needed for calculations (not meant to be
// seen by the user). Once you have this in place,
// you can drop the quarters on your rows and then
// select a date from the InventoryFact table.
// Use this measure to give you what you want.
// This formula works on the assumption that
// there's only one year's worth of data. If you
// have many years, you'll have to adjust the
// formula to account for this.

[Inventory] =
var MaxDateSelected = MAX( InventoryFact[Date] )
var InventoryCount =
    SUMX(
        Quarters,
        var QuarterStart = Quarters[QuarterStart]
        var QuarterEnd = Quarters[QuarterEnd]
        var InventoryCount =
            CALCULATE(
                SUM( FactTable[Count] ),
                FactTable[Date] >= QuarterStart,
                FactTable[Date] <= QuarterEnd,
                FactTable[Date] <= MaxDateSelected,
                REMOVEFILTERS( FactTable[Date] )
            )
        return
            // If you want to show 0's for the quarters
            // that have not yet been in scope, then
            // add 0, else return just InventoryCunt.
            InventoryCount + 0
    )
return
    InventoryCount

[v-cazheng-msft]

Hi @AEVMSP,

You may try this solution, create a Measure as follows.

DynamicCount =
VAR filter_minVal =
    CALCULATE (
        MIN ( 'calendar'[Date] ),
        FILTER (
            'calendar',
            'calendar'[COL MonthYear] = SELECTEDVALUE ( 'calendar'[COL MonthYear] )
        )
    )
VAR maxDateForQuarter =
    CALCULATE (
        MAX ( 'calendar'[Date] ),
        ALLEXCEPT ( 'calendar', 'calendar'[Date].[Quarter] ),
        TREATAS ( VALUES ( 'Table'[Date] ), 'calendar'[Date] )
    )
VAR res =
    IF (
        maxDateForQuarter < filter_minVal,
        CALCULATE (
            SUM ( 'Table'[Count] ),
            MONTH ( 'Table'[Date] ) = MONTH ( maxDateForQuarter )
        ),
        CALCULATE (
            SUM ( 'Table'[Count] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = MONTH ( filter_minVal ) )
        )
    )
RETURN
    IF ( ISBLANK ( res ), 0, res )

The result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,                                           

Community Support Team _ Caiyun

[daXtreme · Accepted solution]

Hi @AEVMSP 

In fact, this is very simple. Just add to your table a column that will store quarters. When you then put the quarters in your table and create this measure (below) you'll get what you want. For instance, put the quarters on the columns and have a slicer with dates (select a date on it and see what happens when you drop the measure on the canvas).

// To perform what you want you have to
// create a table called Quarters and 
// keep it disconnected. One of the columns
// will be Quarter (Q1, Q2, Q3, Q4), another
// will store the start date of the quarter
// and the third one will store the very last
// date of the quarter (QuarterStart, QuarterEnd).
// You can hide the last two columns as they are
// only needed for calculations (not meant to be
// seen by the user). Once you have this in place,
// you can drop the quarters on your rows and then
// select a date from the InventoryFact table.
// Use this measure to give you what you want.
// This formula works on the assumption that
// there's only one year's worth of data. If you
// have many years, you'll have to adjust the
// formula to account for this.

[Inventory] =
var MaxDateSelected = MAX( InventoryFact[Date] )
var InventoryCount =
    SUMX(
        Quarters,
        var QuarterStart = Quarters[QuarterStart]
        var QuarterEnd = Quarters[QuarterEnd]
        var InventoryCount =
            CALCULATE(
                SUM( FactTable[Count] ),
                FactTable[Date] >= QuarterStart,
                FactTable[Date] <= QuarterEnd,
                FactTable[Date] <= MaxDateSelected,
                REMOVEFILTERS( FactTable[Date] )
            )
        return
            // If you want to show 0's for the quarters
            // that have not yet been in scope, then
            // add 0, else return just InventoryCunt.
            InventoryCount + 0
    )
return
    InventoryCount

[AEVMSP]

Thank you @daXtreme. It does what I asked during the quarter, but when I pick the 1st month of the second quarter Q1 goes to zero. My quarters are off (Aug is the last month of Q1, Sep is the 1st month of Q2), but the pics below show last month Q1 and 1st month Q2 filtered.

Q1

Q2

[daXtreme]

@AEVMSP 

I've checked it and the measure is correct. Works OK. You must have messed it up somehow. But no worries. Here's a file that shows how it works. Just download it and have a look. When you work in PBI, you should remember that it does matter what table you choose your columns from. The iron rule of a good design (and bug-free at that) is that fact tables' columns should almost always be hidden and slicing should be done only via dimensions.

[daXtreme · Accepted solution]

@AEVMSP 

The table with quarters must not be connected to any table. I'll quickly check if the measure works the way I think it does. It should for all intents and purposes... but I have not checked it against a model.

[AEVMSP]

I didn't read one of your comments in the formula. I have multiple years of data, so that was why it didn't work. Thx for your help!