Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tyleharris
Frequent Visitor

Totaling Values Based on an Adjusted Date

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 IDSales Amount NetSales Quantity NetYearAdjusted DateAdjusted Sales Amount
37550$21,149.02199020161/14/20171990
37550$125,423.791199520172/4/201711995
37550$156,864.291423620182/3/201814236
37550$51,099.99469920192/4/20194699

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

YearSales Amount NetSales Quantity Net
2017$123,378.9111803
2018$156,864.2914236
2019$51,099.994699
 
Thanks!
7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 IDSales Amount NetSales Quantity NetGregorian YearAdjusted DateAdjusted Sales Amount
37550$21,149.02199020161/14/2017 0:0031037
37550$125,423.791199520172/4/2017 0:0030181
37550$156,864.291423620182/3/2018 0:0017608
37550$54,583.90499820192/4/2019 0:003112


It needs to be displayed like this:

Gregorian YearSales Amount NetSales Quantity Net
2017$123,378.9111803
2018$156,864.2914236
2019$54,583.904998
 Total:31037


Thanks!

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) 
    )
)

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 IDSales Amount NetSales Quantity NetGregorian YearAdjusted DateAdjusted Sales
37550$21,149.02199020161/14/2017 0:00 
37550$125,423.791199520172/4/2017 0:0010947
37550$156,864.291423620182/3/2018 0:0012610
37550$54,583.90499820192/4/2019 0:003112
    Total31037


Yearly totals should be:

Gregorian YearSales Amount NetSales Quantity Net
2017$123,378.9111803
2018$156,864.2914236
2019$54,583.904998
 Total31037


Thank you!

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Untitled.png

 

 
Thank you!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.