Last Resort Question & Desperate - Advanced Power BI Fixed Calculations for Categories

[Anonymous]

Hi All,

This is my fourth post regarding this topic wihtout success and my last attempt to try to get help from the community to solve it. It was easier to do in Tableau with Fixed Calculations at various levels, but I am STRUGGLING to do it in Power BI. This report has taken me a little over a year, and I'm in the same place.

There are five categories these customers - items should go in, and I have provided a sample that contains each in theory. The end-user wants to have this dynamic

Current Period is whatever the user selects using the date table

The previous Period is that range - 365

Customer Start: Customer Create Date is within the user's selected window. In this case, 1/1/2021 - 1/31/2021

Customer Stop - Customer Create Date is outside the current Period, and there are no transactions at a CUSTOMER level in the current Period. I created the Customer Volume column in SQL to try to aggregate this and ignore the items since DAX wasnt working. This means If I am customer A and I bought items 1,2,3 but no items in the current period.

Product Start - Customer Create Date is outside the current Period, and there are no transactions in the previous Period, but there are some at the Customer Item level in the current Period. This means I bought 0 items in the previous period but did buy some in the filtered period and am an existing customer

Product Stop - Customer Create Date is outside the current Period, and there are no transactions in the current Period, but there are some at the Previous Item Level in the previous Period. This means If I am customer A and I bought items 1,2,3 but bought items 1,2 in the current period, Item 3 would be Product Stop.

Volume - Transactions exist in the current and previous period. (asks as the "Else")

Based on my sample data. The categorization should be as follows. As I mentioned, I have spent hundreds of hours on this report trying to replicate something I created in Tableau and even mixing in SQL to get the Volume at the Customer Category since Power BI can't replicate the fixed calculations, I just simply hit a roadblock and can't do anymore.

Any help would be greatly appreciated, and I know there are more advanced people than me in DAX, and I am hoping they can help out.

The sample workbook is below:

https://drive.google.com/file/d/1MP2XoNaYF-CyrMHXuNP4hiRw3aIx0nLm/view?usp=sharing

cust_num	item	Driver
8	0KC4401062X60X60	Product Stop
8	13800RF125A0400	Product Stop
8	13850RF062A0100	Product Stop
8	13850RF062B0600	Product Stop
8	13860FF062A0150	Product Stop
8	13860FF062A0250	Product Start
8	13860FF062A0300	Product Stop
8	13860FF062A0400	Product Start
8	13860RF062A0200	Product Start
8	13860RF062A0250	Product Start
8	13860RF062A0300	Volume
8	13860RF062A0400	Product Start
8	13860RF062A0500	Product Start
8	13860RF062A0600	Product Start
8	13860RF062A0800	Product Start
8	13860RF062A1000FC	Product Start
8	13860RF062A1400FC	Product Start
8	13860RF062A1600FC	Product Start
8	13860RF125A1600FC	Product Stop
8	13880FF062A0150FC	Product Stop
8	13880FF062A0200FC	Product Start
8	13880FF062A0300FC	Product Stop
8	13880FF062A0400FC	Product Start
8	13880FF062A0600FC	Product Start
8	13880FF062A0800FC	Product Start
8	13880FF062A1200FC	Product Start
8	13880FF125A0150FC	Product Stop
8	13880FF125A0600FC	Product Start
8	13880FF125A1200FC	Product Stop
8	13880FF125B0300FC	Product Stop
8	13880RF125A0400FC	Product Start
8	13880RF125A1600FC	Product Stop
8	13G3000FF062A0600	Product Stop
8	13G3000FF062A0800	Product Stop
8	13G3000FF062A1000	Product Stop
8	13G3000FF062A1200	Product Stop
8	13G3000FF062A1400	Product Stop
8	13G3000FF062B0600	Product Stop
8	13G3000FF062B0800	Product Stop
8	13G3000FF062B1000	Product Stop
8	13G3000FF062B1200	Product Stop
8	13G3000FF062B1400	Product Stop
8	3TCNA400	Product Stop
8	3TLSM100SP	Product Start
12	PUL007X14X22	Customer Stop
30	2547A	Customer Start
30	2547D	Customer Start
16991	3THE075C	Customer Start

[AlB · Accepted solution]

Hi @Anonymous 

1. The expected result seems to be wrong for

       - Customer 8, Item 13880FF062A0300FC

       - Customer 30

2. If you want a dynamic period, you'll need to do this through a measure, not a calculated column. 

3. The DAX logic is not overly complicated, only a bit cumbersome to write. Create a measure as below. See it all at work in the attached file.

4. As a side comment, I would strongly discourage the use of the Auto Date/Time option.

DriverMeasure = 
VAR createdDate_ = DATEVALUE ( SELECTEDVALUE ( 'Invoice Variance'[createdate] ) ) //Watch out, createddate column actually of DateTime type
VAR currentPeriod_ = DISTINCT ( 'Current Period'[Date] )
VAR previousPeriod_ = DATEADD ( 'Current Period'[Date], -1, YEAR )
VAR custTransCurrentPeriod_ =
    CALCULATE (
        COUNT ( 'Invoice Variance'[inv_date] ),
        TREATAS ( currentPeriod_, 'Invoice Variance'[inv_date] ),
        ALLEXCEPT ( 'Invoice Variance', 'Invoice Variance'[cust_num] )
    ) + 0
VAR itemTransPreviousPeriod_ =
    CALCULATE (
        COUNT ( 'Invoice Variance'[inv_date] ),
        TREATAS ( previousPeriod_, 'Invoice Variance'[inv_date] )
    ) + 0
VAR itemTransCurrentPeriod_ =
    CALCULATE (
        COUNT ( 'Invoice Variance'[inv_date] ),
        TREATAS ( currentPeriod_, 'Invoice Variance'[inv_date] )
    ) + 0
VAR createdInPeriod_ = createdDate_ IN currentPeriod_
RETURN
    SWITCH (
        TRUE (),
        createdInPeriod_, "Customer Start",
        NOT createdInPeriod_ && custTransCurrentPeriod_ = 0, "Customer Stop",
        itemTransPreviousPeriod_ = 0 && itemTransCurrentPeriod_ > 0, "Product Start",
        itemTransPreviousPeriod_ > 0 && itemTransCurrentPeriod_ = 0, "Product Stop",
        itemTransPreviousPeriod_ > 0 && itemTransCurrentPeriod_ > 0, "Volume"
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.