Last sales in Calendar Limiter

Aza33ello — Tue, 06 Jun 2023 13:19:48 GMT

Hello everybody,

I'm trying to make a DAX measure, to find last non empty cell with date limiter. So if there is no sales in current period, the measure should dive into past for 6 month and no longer, and takes the first non blank value.

For example,

Jan 2023 - is a current date

IF Jan 2022 has no sales, it should go to the past between Dec 2021 and Jul 2021.

This is my code:

=VAR curYM = MIN ( 'Calendar'[YM number]) VAR prevY_YMs = CALENDAR(curYM-18; curYM-12) VAR sales = SUMX ( VALUES ( ACTSALES3[Product] ); VAR addSales = ADDCOLUMNS ( prevY_YMs; "@sales"; VAR ym = [Date] VAR s = CALCULATE ( SUM ( ACTSALES3[Sales TRUB] ); ALL ( 'Calendar' ); 'Calendar'[YM number] = DATE(YEAR(ym); MONTH(ym);1) ) RETURN s ) VAR filterYMWithSaleRows = FILTER ( addSales; [@sales] <> 0 ) VAR lastYMWithSaleRows = MAXX ( filterYMWithSaleRows; [Date] ) VAR filterLMdata = FILTER ( filterYMWithSaleRows; [Date] = lastYMWithSaleRows ) VAR result = IF ( NOT ISEMPTY ( filterYMWithSaleRows ); SUMX ( filterLMdata; [@sales] ) ) RETURN result ) RETURN sales

If I change VAR prevY_YMs =CALENDAR(curYM-18; curYM-12) to GENERATESERIES it will work perfectly.

But the question is, to avoid this function. Any ideas?

Re: Last sales in Calendar Limiter

lbendlin — Thu, 08 Jun 2023 00:12:59 GMT

CALENDAR(curYM-18; curYM-12)

this will not work as each argument of CALENDAR expects a full date, not just a month number.

var currD = min ('Calendar'[Date]) var prevY_YMs = CALENDAR(EDATE(currd,-18),EDATE(currD,-12))

Note this will create a series of dates, not months.

Re: Last sales in Calendar Limiter

Aza33ello — Thu, 08 Jun 2023 07:57:18 GMT

Whoa, it works. Completly forgot about EDATE. Thank you so much, you literally saved my life!!!

topic Re: Last sales in Calendar Limiter in DAX Commands and Tips

Last sales in Calendar Limiter

Re: Last sales in Calendar Limiter

Re: Last sales in Calendar Limiter