Simplifying DAX for POS Data

[ariana_night]

Hello,

I have been asked to revise some current measures that someone created before my time. I have spent the last 4 days researching and rewriting the DAX and nothing works. I have to revise measures for: this month, last month, this quarter, last quarter. 

Please see the current DAX measures below for reference. Any feedback on minimizing the amount of DAX that is used in these measures is GREATLY APPRECIATED. 

last mo POS = 
VAR this_wk = CALCULATE(MAX('POS Weekly All Accounts Sales'[Week End Date]))

RETURN
CALCULATE(
'POS Weekly All Accounts Sales'[POS Units Sold], 
'Calendar'[Week End Date] = this_wk -28
|| 'Calendar'[Week End Date] = this_wk - 35
|| 'Calendar'[Week End Date] = this_wk - 42
|| 'Calendar'[Week End Date] = this_wk - 49
)


this mo POS = 
VAR this_wk = CALCULATE(MAX('POS Weekly All Accounts Sales'[Week End Date]))

RETURN 
CALCULATE(
'POS Weekly All Accounts Sales'[POS Units Sold], 
'Calendar'[Week End Date] = this_wk
|| 'Calendar'[Week End Date] = this_wk - 7
|| 'Calendar'[Week End Date] = this_wk - 14
|| 'Calendar'[Week End Date] = this_wk - 21
)


last Q POS = 
VAR this_wk = CALCULATE(MAX('POS Weekly All Accounts Sales'[Week End Date]))

RETURN
CALCULATE(
'POS Weekly All Accounts Sales'[POS Units Sold], 
'Calendar'[Week End Date] = this_wk - 91
|| 'Calendar'[Week End Date] = this_wk - 98
|| 'Calendar'[Week End Date] = this_wk - 105
|| 'Calendar'[Week End Date] = this_wk - 112
|| 'Calendar'[Week End Date] = this_wk - 119
|| 'Calendar'[Week End Date] = this_wk - 126
|| 'Calendar'[Week End Date] = this_wk - 133
|| 'Calendar'[Week End Date] = this_wk - 140
|| 'Calendar'[Week End Date] = this_wk - 147
|| 'Calendar'[Week End Date] = this_wk - 154
|| 'Calendar'[Week End Date] = this_wk - 161
|| 'Calendar'[Week End Date] = this_wk - 168
|| 'Calendar'[Week End Date] = this_wk - 175
)


this Q POS = 
VAR this_wk = CALCULATE(MAX('POS Weekly All Accounts Sales'[Week End Date]))

RETURN 
CALCULATE(
'POS Weekly All Accounts Sales'[POS Units Sold], 
'Calendar'[Week End Date] = this_wk
|| 'Calendar'[Week End Date] = this_wk - 7
|| 'Calendar'[Week End Date] = this_wk - 14
|| 'Calendar'[Week End Date] = this_wk - 21
|| 'Calendar'[Week End Date] = this_wk - 28
|| 'Calendar'[Week End Date] = this_wk - 35
|| 'Calendar'[Week End Date] = this_wk - 42
|| 'Calendar'[Week End Date] = this_wk - 49
|| 'Calendar'[Week End Date] = this_wk - 56
|| 'Calendar'[Week End Date] = this_wk - 63
|| 'Calendar'[Week End Date] = this_wk - 70
|| 'Calendar'[Week End Date] = this_wk - 77
|| 'Calendar'[Week End Date] = this_wk - 84
)

[ariana_night]

Hi:

The table looks like this (using Month over Month example):

This Month and Last Month POS Units sold in 2 different columns. I thought using  DATESBETWEEN or DATESINPERIOD would shorten the DAX but either it does not work or I am not using them correctly. As far as relationships, the 'pos weekly all accounts [week end date]' and 'calendar [week end date]' connected. I hope I answered your question.

[Icey]

Hi @ariana_night ,

Please try to create your measures like so:

last mo POS =
CALCULATE (
    'POS Weekly All Accounts Sales'[POS Units Sold],
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), - 1, MONTH )
)

Best Regards,

Icey

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

[Whitewater100]

Hi:

Do you have your pos results with a date field in separate table? If you have a separate date table, all those calculations are very straightforward. If you feel like sharing an example I will check it out a bit later today. Thanks..