DAX- Pre and Post Category averages on specific date

[veeranc]

Hi everyone, 

I am struggling with DAX for this poblem, 

Data looks like this (4th column I added for reference)

Date	Category	Value
1/1/2023	A	12
1/3/2023	D	22
1/6/2023	A	34
1/7/2023	D	19	Pre 2
1/8/2023	A	7
1/10/2023	S	22
1/12/2023	A	34
1/27/2023	S	12	Pre 2
2/14/2023	D	13	Pre 1
4/3/2023	A	24	Pre 2
4/5/2023	S	16	Pre 1
4/7/2023	A	13	Pre 1
4/18/2023	D	11	Post 1
5/1/2023	A	5	Post 1
5/2/2023	S	15	Post 1
5/2/2023	S	4	Post 2
5/3/2023	D	7	Post 2
5/4/2023	A	5	Post 2
5/23/2023	D	15
5/30/2023	S	21

On any specific day which is in another table for example 4/8/23 for A, D and S. They could also say just 5/10/23 for A and D

I need Pre and Post averages of 2 values, then calculate the difference

Category	A	D	S
Previous 2	18.5	16	14
Post 2	5	14	9.5
Difference	13.5	2	4.5

any help is appreciated

[veeranc]

Thanks @Anonymous 
I guess my problem was not clear. I could not attach the pbix file with data which would have been easier.

I Have two Tables - TableSales and TablePromos

TableSales has Product, Category, SaleDate and SaleAmount

TablePromos has  Product and PromoDates

At any Promo date slected, I wanted the difference of sales average for each category previous 2 days to following 2 days that Product.

User first chooses Product and then selects listed PromDate

Result should be table - Category, Prepromo, PostPromo and difference

I am trying to Rank based on Promodate and then pick 2 values pre and post for averages. I am getting errors.

Appreciate your help.

Thanks

Veera

[Anonymous]

Hi @veeranc,

If you mean to list the previous and post average based on selection, you can take a look at the following measure formula:

formula =
VAR newCategory =
    SELECTEDVALUE ( NewTable[NewColumn] )
VAR currDate =
    MAX ( Table1[PromDate] )
VAR prev =
    CALCULATE (
        AVERAGE ( Table1[Values] ),
        FILTER ( ALLSELECTED ( Table1 ), [Date] >= currDate - 2 && [Date] < currDate ),
        VALUES ( Table1[Category] )
    )
VAR post =
    CALCULATE (
        AVERAGE ( Table1[Values] ),
        FILTER ( ALLSELECTED ( Table1 ), [Date] > currDate && [Date] <= currDate + 2 ),
        VALUES ( Table1[Category] )
    )
RETURN
    SWITCH ( newCategory, "Previous", prev, "Post", post, prev - post )

You also need a new table with different type strings to use as matrix row and expand the calcations, raw table category field as column.

Regards,

Xiaoxin Sheng

[veeranc]

Hi @Anonymous 

Thanks for the solution, I arrived at similar solution. If sale dates are continous then this works. If there are holidays or weekends or some other reason, then 2 day calculation won't work. I have to dynamically pick last two sale dates. That's where I am stuck now. That's the reason for trying out RANK.

Thanks

Veera

[Anonymous]

HI @veeranc,

Perhaps you can try to use the following measure formula to use the new category column as condition to redirect to different calculation expressions:

formula =
VAR newCategory =
    SELECTEDVALUE ( Table1[NewColumn] )
RETURN
    IF (
        newCategory <> "Difference",
        CALCULATE (
            AVERAGE ( Table1[Values] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                SEARCH (
                    SWITCH ( newCategory, "Previous", "Pre", "Post", "Post", "" ),
                    Table1[NewColumn],1,-1
                    ) > 0
            ),
            VALUES ( Table1[Category] )
        ),
        CALCULATE (
            CALCULATE (
                AVERAGE ( Table1[Values] ),
                SEARCH ( "Pre", Table1[NewColumn], 1, -1 ) > 0
            )
                - CALCULATE (
                    AVERAGE ( Table1[Values] ),
                    SEARCH ( "Post", Table1[NewColumn], 1, -1 ) > 0
                ),
            ALLSELECTED ( Table1 ),
            VALUES ( Table1[Category] )
        )
    )

Regards,

Xiaoxin Sheng