Totaling Values Based on an Adjusted Date

[tyleharris]

I'm new to DAX so bare with with me!

I've been tasked with totalling dollars and units, year over year, ignoring the first month worth of sales (viewed as stock buildup that we don't want to count). I created a measure that finds a specific licensee's initial purchase date then addes 31 days to it to get an adjusted date:

Adjusted Date = MINX(

    KEEPFILTERS(VALUES('Licensee'[Licensee ID])),

    CALCULATE(MIN('Sales Transactions'[Transaction Date]))

) + 31

It's from this adjusted date I want to calculate dollars/units of each specific licensee.

Below is my working dataset and the measure I tried for adjusted sales amounts:

Adjusted Sales Amount = CALCULATE(SUM('Sales Transactions'[Sales Quantity Net]), FILTER('Sales Transactions', 'Sales Transactions'[Transaction Date]>=CALCULATE(MIN('Sales Transactions'[Transaction Date]))))

It returns the same values as sales quantitiy net so I'm clearly missing an important function or proper syntax.

Licensee ID	Sales Amount Net	Sales Quantity Net	Year	Adjusted Date	Adjusted Sales Amount
37550	$21,149.02	1990	2016	1/14/2017	1990
37550	$125,423.79	11995	2017	2/4/2017	11995
37550	$156,864.29	14236	2018	2/3/2018	14236
37550	$51,099.99	4699	2019	2/4/2019	4699

I need to get the values below which are just hard filtered on or after 1/14/17:

Year	Sales Amount Net	Sales Quantity Net
2017	$123,378.91	11803
2018	$156,864.29	14236
2019	$51,099.99	4699

 
Thanks!

[Ashish_Mathur]

Hi,

I cannot understand your question.  IN your formula, you refer to a Transaction date but there is no such column in your pasted tables.  Please share the dataset and expain how you arrived at the end result of quantity and sales.

[v-shex-msft]

HI @tyleharris,

I think you need to use allselected function on your table to break current row contents and let formula looping whole table.

Adjusted Sales Amount =
VAR currDate =
    MIN ( 'Sales Transactions'[Transaction Date] )
RETURN
    CALCULATE (
        SUM ( 'Sales Transactions'[Sales Quantity Net] ),
        FILTER (
            ALLSELECTED ( 'Sales Transactions' ),
            'Sales Transactions'[Transaction Date] >= currDate
        )
    )

In addition, I'd like to suggest you add a calendar table and use calendar date as table visual fields for measure calculate.

Understanding ALLSELECTED

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,
Xiaoxin Sheng

[tyleharris]

Hi @v-shex-msft 

Thank you for your reply. It definately has me heading in the right direction. However, it's not quite right.

I took your measure and changed it a little from a piece that was given to me from a coworker:

Adjusted Sales Amount =

VAR currDate =

    MINX(

    KEEPFILTERS(VALUES('Licensee'[Licensee ID])),

    CALCULATE(MIN('Sales Transactions'[Transaction Date]))

) + 30

RETURN

    CALCULATE (

        SUM ( 'Sales Transactions'[Sales Quantity Net] ),

        FILTER (

            ALLSELECTED( 'Sales Transactions' ),

            'Sales Transactions'[Transaction Date] >= currDate

        )

    )

I receive the correct total of 31037 which is the good news, but I need it to calculate the year over year - Which it appears to not be displaying correctly:

Licensee ID	Sales Amount Net	Sales Quantity Net	Gregorian Year	Adjusted Date	Adjusted Sales Amount
37550	$21,149.02	1990	2016	1/14/2017 0:00	31037
37550	$125,423.79	11995	2017	2/4/2017 0:00	30181
37550	$156,864.29	14236	2018	2/3/2018 0:00	17608
37550	$54,583.90	4998	2019	2/4/2019 0:00	3112

It needs to be displayed like this:

Gregorian Year	Sales Amount Net	Sales Quantity Net
2017	$123,378.91	11803
2018	$156,864.29	14236
2019	$54,583.90	4998
	Total:	31037

Thanks!

[d_gosbell]

I think some of your attempts so far are filtering out the first 31 days each year, not just the first 31 days of the first year. Something like the following may be more what you are looking for

Adjusted Sales = SUMX(
    VALUES(Licensee[Licensee ID])
    ,CALCULATE(SUM('Sales Transactions'[Sales Amount Net])
        ,FILTER(ALL('Sales Transactions'[Transaction Date]), 'Sales Transactions'[Transaction Date] > CALCULATE( MIN('Sales Transactions'[Transaction Date]), ALL('Sales Transactions')) + 31) 
    )
)

[tyleharris]

Hi @d_gosbell 

Getting closer! I adjusted your formula to sum of Sales Quantity instead of the Sales Amount since thats what I've been working on at the moment. You are correct in that I need to filter out the first 31 days of sales of the first year. 

The formula is returning the correct total of 31037, but is still displaying the yearly sums incorrectly. It looks like the yearly sums are still filtering out the first 31st days of each year.

Licensee ID	Sales Amount Net	Sales Quantity Net	Gregorian Year	Adjusted Date	Adjusted Sales
37550	$21,149.02	1990	2016	1/14/2017 0:00
37550	$125,423.79	11995	2017	2/4/2017 0:00	10947
37550	$156,864.29	14236	2018	2/3/2018 0:00	12610
37550	$54,583.90	4998	2019	2/4/2019 0:00	3112
				Total	31037

Yearly totals should be:

Gregorian Year	Sales Amount Net	Sales Quantity Net
2017	$123,378.91	11803
2018	$156,864.29	14236
2019	$54,583.90	4998
	Total	31037

Thank you!

[v-shex-msft]

Hi @tyleharris ,

Please take a look at following blog about deal with measure total level calculation issue :

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

[tyleharris]

Hi @v-shex-msft ,

I made some progress on my formula - It totals the yearly sale quantities correctly, but now the grand totals are off. I've narrowed down the problem, but not sure how to fix it - As the formula currently stands the grand total is the sum of both Licensee IDs quantities, but only from Licensee ID 37750 adjusted first purchase date (their first purchase date +31 days) since it's date is before Licensee ID 81706. What I need this formula to do is look at each unique Licensee ID first purchase date, add the 31 days, then start summing the quantities from that date. I have it working for the individual Licensee Yearly Totals, but as I said the grand totals are not summing correctly.

Adjusted Sales Quantity = SUMX(

    VALUES(Licensee[Licensee ID])

    ,CALCULATE(SUM('Sales Transactions'[Sales Quantity Net])

        ,FILTER(ALL('Sales Transactions'[Transaction Date]), 'Sales Transactions'[Transaction Date] >= CALCULATE( MIN('Sales Transactions'[Transaction Date]), ALLSELECTED('Sales Transactions')) + 31)

    )

)

Power BI Results in Excel - The top table is the working data using the formula above. The bottom two tables are the same data, but hard filtered seperately to show what the results should be.

Thank you!