Maximum Consumption for Material 'Y'. Evaluating every 30-day window within range.

[djkoenig]

Hello Experts! 

I am pretty new to DAX and was hoping you all would be able to help me out with a formula/data set-up. I've tried (more than) a few iterations of this calculation and have not have been successful. 

My requirement is to evaluate the consumption of multiple materials within 30 day range increments and then to select the 30 day window where consumption is the greatest. This window needs to fall within the last 365 calendar days.

Example: 

Material X = 3 units, Posted 9/30/2022

Material Y = 4 units, Posted 9/1/2022

Material X = 5 units, Posted 9/4/2023

Material X = 6 units, Posted 7/24/2023

Material Y = 2 units, Posted 8/31/2022

Material Y = 3 units, Posted 8/31/2022

In this example, Material X has a maximum one-time consumption of 6, but the desired output would be 8 (3+5 as posting ranges for material X are within 30 days of one another). 

Material Y will be 4 as there is only one posting within the past 365 days. 

If there is a fomula to return the start date range of the 30 day max consumption window that would also be awesome! Id certainly settle for just the raw consumption numbers though (i.e. Material X=8, Y=4).

It appears that .pbix and .xlsx are unsupported unless I have different permissions. I have a sample data set ready to go as a .pbix if needed/permissions are granted. 

Thanks for looking at this! I appreciate it! 

[OwenAuger]

Hi @djkoenig 

Yes, I can certainly help with this.

The basic idea will be to use a function like TOPN or MAXX to iterate over the possible ranges and return the required values, which can be the start date of the 30-day window or the consumption within that window.

Could you share your existing .pbix or .xlsx file by sharing via OneDrive, Google Drive or similar, and posting the link here?

Direct attachments are only possible for super users unfortunately 😞

Regards

[djkoenig]

Hello @OwenAuger , 

Thanks for the prompt response! If you could assist, you'll have saved me about 4 headaches. Here is the link to the .pbix via OneDrive. You might end up with a permissions error here. If you do please use the g-drive link.

Worst case scenario I also attached the .xlsx. 

*All of the pertinent data within the .pbix can be found inside the table 'BaseTable'. 

OneDrive

 30 Day Max Sample.pbix

G-Drive

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

Excel

https://docs.google.com/spreadsheets/d/1-_M5H1ugoFnmjYTRHcKHNx8vI8hsNzEC/edit?usp=sharing&ouid=10877...

[Anonymous]

Hi @djkoenig ,

I suggest you to try code as below to create a measure.

Count = 
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[Material],
        "IF",
            VAR _30DAYRANGE =
                TODAY () - 30
            VAR _365DAYRANGE =
                TODAY () - 365
            RETURN
                IF (
                    MAX ( 'Table'[PostingDate] ) < _365DAYRANGE,
                    CALCULATE (
                        SUM ( 'Table'[SUMIF] ),
                        FILTER ( 'Table', 'Table'[PostingDate] = MAX ( 'Table'[PostingDate] ) )
                    ),
                    CALCULATE (
                        SUM ( 'Table'[SUMIF] ),
                        FILTER ( 'Table', 'Table'[PostingDate] > _30DAYRANGE )
                    )
                )
    )
RETURN
    SUMX ( _SUMMARIZE, [IF] )

Here is the result for your first sample and the sample in your excel.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djkoenig]

Hi Rico (@Anonymous) , 

Thanks for taking the time to look at this. I have a feeling that's quite close to what I need. However, I will be pulling from live data, so the SumIF and MaxIF in the Excel workbook were more proof of concept. I can arrive at the right answer not using PowerBI, but the requirement is to use PowerBI so here we are! 

So, I guess what I am asking is if we dont have the SumIF and MaxIF columns, what's the best way to create this measure? How do we move the equivalent of my SumIF Excel formula into DAX, while still keeping what you have written above?

Also, a point of concern, the values seem a bit high for the materials, especially 3000725. I manually counted 6, and that is also what my final MaxIF calculation shows within the Excel workbook. Screenshot with base data table filtered for only 3000725. Highlighted section is what my Excel formula is picking up and should be the desired output. 

[OwenAuger · Accepted solution]

Hi @djkoenig 

I've just managed to look at this properly myself.

I have attached a PBIX with some suggested measures which I think achieve what you are looking for.

Also, you really need to create a Date table and mark it as a date table as I have in attached PBIX.

Goods Movement = 
SUM ( BaseTable[+/- GoodsMovement] )

Goods Movement 30 Day Window = 
CALCULATE (
    [Goods Movement],
    DATESINPERIOD (
        'Date'[Date],
        MIN ( 'Date'[Date] ),
        30,
        DAY
    )
)

Start Date of Top 30 Day Window = 
VAR TodayDate = TODAY ( )
VAR DateRange =
    DATESINPERIOD ( 'Date'[Date], TodayDate, - 365, DAY )
VAR TopPeriodStart =
    INDEX (
        1,
        DateRange,
        ORDERBY (
            [Goods Movement 30 Day Window],
            DESC,
            'Date'[Date],
            DESC
        ),
        DEFAULT
    )
RETURN
    TopPeriodStart

Goods Movement Top 30 Day Window per Material v2 = 
VAR TopPeriodStart =
    ADDCOLUMNS (
        VALUES ( BaseTable[Material] ),
        "@WindowStart",
        [Start Date of Top 30 Day Window]
    )
VAR MaterialDateFilter =
    GENERATE (
        TopPeriodStart,
        DATESINPERIOD ( 'Date'[Date], [@WindowStart], 30, DAY )
    )
RETURN
    CALCULATE (
        [Goods Movement],
        KEEPFILTERS ( MaterialDateFilter )
    )

And a more verbose version that doesn't depend on [Start Date of Top 30 Day Window]:

Goods Movement Top 30 Day Window per Material = 
VAR TodayDate = TODAY ( )
VAR DateRangeMaterial =
    CROSSJOIN (
        DATESINPERIOD ( 'Date'[Date], TodayDate, - 365, DAY ),
        VALUES ( BaseTable[Material] )
    )
VAR TopPeriodStart =
	SELECTCOLUMNS (
	    INDEX (
	        1,
	        DateRangeMaterial,
	        ORDERBY (
	            [Goods Movement 30 Day Window],
	            DESC,
	            'Date'[Date],
	            DESC
	        ),
	        DEFAULT,
	        PARTITIONBY ( BaseTable[Material] )
	    ),
	    "@Material", BaseTable[Material],
	    "@WindowStart", 'Date'[Date]
    )
VAR MaterialDateFilter =
    SELECTCOLUMNS (
        GENERATE (
            TopPeriodStart,
            SELECTCOLUMNS (
                DATESINPERIOD ( 'Date'[Date], [@WindowStart], 30, DAY ),
                "@WindowDate", 'Date'[Date]
            )
        ),
        "@Material", [@Material],
        "@WindowDate", [@WindowDate]
    )
RETURN
    CALCULATE (
        [Goods Movement],
        KEEPFILTERS ( MaterialDateFilter )
    )

I have made a couple of assumptions here:

1. Consider 30 day periods starting between 365 days ago up to today (using TODAY () function)
2. If multiple 30 day periods are tied for the top Goods Movement value, choose the latest.

The final measure looks like this when displayed by Date/Material:

Does this look like the right result and are you able to get it working at your end?

Regards

[djkoenig]

Definitely worthy of that SuperUser title @OwenAuger! That was exactly what I needed, right down to the inclusion of the "verbose" calculation. Thank you! Wish I could double thumbs up this one 😁