Use Prior Month Average when Sales Transaction is missing Freight/lb

DAX DAX Calculations
1

I’ve worked out the Previous Month Avg Freight/lb utilizing DateAdd -1 Month and it works fine when placed in the context of a month.

Previous Month

However I’m showing Sales Transaction Detail that is at a Day level. I’m trying to accomplish the following, for a sales transaction that happens on 5/17/22 which has no actual freight/lb value (I have a calculated column that checks, (“Has Freight”/“No Freight”) I want to return the average freight/lb from April 2022 and use that value. In other words, if a Transaction has “No Freight”, use the prior month average.

Detail
Detail675×533 30.2 KB

When I place my Previous Month Freight/lb measure in a table visual, it’s not calculating correctly.

Incorrect Calc
Incorrect Calc913×200 12.9 KB

2

Hi ,

It will be helpful for me or any form member to help you if you provide work in progress pbix.

Thanks

3

I understand, I was hoping for some guidance without and maybe this will help simplify it. In a table visual, if my row context is Dates, I need to modify the filter context so that I can display the prior month average. Example, if row context is 2/15/2022, I need the average for January 2022. Trying to compare current actual results by date vs the prior month average.

4

Here is a modified pbix file, hopefully this will help

Freight Sample.pbix (41.9 KB)

5

I’m still stuck on this one, seems like it should be simple, but I’m overlooking something. Thanks in advance!

6

I believe I may have figured it out.
Avg Freight/lb 1M Ago =

CALCULATE (

AVERAGEX ( VALUES ( Freight[Customer Number] ), [Freight/lb] ),

DATEADD ( Dates[Date], -1, MONTH ),

ALL ( 'Freight' )

)

1 Like
7

Hi @sscott, thanks for starting this discussion and for sharing your solution. If this query has been solved kindly close the thread by ticking the Solution box.

Thanks