How To Determine If a Dimension Goes Over a Threshold During Any Time

[tstackhouse]

I am tyring to find a way to make a calculation that will flag a customer if they go over a certain threshold during a period of time.

My Columns are as follows:

Customer       Sales Date      Day Supply        Item       MED    

MED is calculated based on the item that is purchased. The MED is a number that I would like to be able to see if a customer goes over at any point in time. My problem with calculating this is that I need to see if the customers "Day Supply" column has over laps with another purchase day.

For example:

Customer X purchases a bottle of asprin with a 7 day supply on August 1st. The bottle has 14 pills inside(2 pills a day). This gives Customer X a MED of 4 per day for those 7 days. (MED is a number given to each product, calcululation is not important) On August 4th Customer X purchases a bottle of tylenol with a 7 day supply. Again, the bottle has 14 pills inside(2 pills a day). The MED for tylenol per day is 3.

Therefore this would Customer X would have the following MED for August 1st- August 14th:

Date: 1st  2nd  3rd  4th  5th  6th  7th  8th  9th  10th  11th  12th  13th  14th

MED:  4      4      4     7     7      7     7     3      3     3        0       0      0        0

I would like to flag any customer that goes over a MED of 6 for any day.

Is there anyway that I am able to do this on Power BI using a DAX calculation or other method??

[v-yulgu-msft]

Hi @tstackhouse,

Could you please describe why MED for asprin per day is 4 since the bottle has 14 pills inside and supply day is 7 (14/7=2). Also, why is MED for tylenol per day 3? Another question, how should I know how many pills are there inside a bottle for different item.

Regards,
Yuliana Gu

[tstackhouse]

Hello @v-yulgu-msft,

Technically, the pill count and pills per bottle is irrelevant. I just wanted to give some context. The MED does not have anything to do with the number of pills per bottle. Aspirin and Tylenol were just used as plug in names and their MED is not actually 4 and 3. Each drug has a MED(which has to do with strength), I would like to calculate if any patients are receiving over a certain amount of MED for any day.

Thank you

[v-yulgu-msft · Accepted solution]

Hi @tstackhouse,

Based on my assumption, I created a data table named as 'Threshold Table' and a calendar table:

Calendar Table = CALENDAR(DATE(2017,8,1),DATE(2017,8,14))

     

In source table, add a calculated column named as [End Date].

End Date =
DATEADD (
    'Threshhold Table'[Sales Date].[Date],
    'Threshhold Table'[Day Supply] - 1,
    DAY
)

Cross join above two tables:

Test1 =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN ( 'Threshhold Table', 'Calendar Table' ),
        "MED2", IF ( [Date] > MAX ( 'Threshhold Table'[End Date] ), 0, 'Threshhold Table'[MED] )
    ),
    ( [Date] >= 'Threshhold Table'[Sales Date]
        && [Date] <= 'Threshhold Table'[End Date] )
        || [Date] > MAX ( 'Threshhold Table'[End Date] )
)

Drag fields from 'Test1' into a Matrix visual.

Best regards,
Yuliana Gu