cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## Last sales in Calendar Limiter

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] );
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?

1 ACCEPTED SOLUTION
Super User
``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.

2 REPLIES 2
Regular Visitor

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

Super User
``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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors