Simplifying DAX for POS Data

ariana_night — Thu, 26 May 2022 15:17:39 GMT

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 )

Re: Simplifying DAX for POS Data

Whitewater100 — Thu, 26 May 2022 16:58:49 GMT

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

Re: Simplifying DAX for POS Data

ariana_night — Thu, 26 May 2022 17:31:59 GMT

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.

Re: Simplifying DAX for POS Data

Icey — Tue, 31 May 2022 08:47:09 GMT

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.

topic Re: Simplifying DAX for POS Data in DAX Commands and Tips

Simplifying DAX for POS Data

Re: Simplifying DAX for POS Data

Re: Simplifying DAX for POS Data

Re: Simplifying DAX for POS Data