cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## How to stop a line

Hi,

I have created 2 measures for the cumulative sales. 1 for Previous fiscal year and 1 for Current Fiscal year for the completed months. At the moment March is still not completed that's why this is not in scope of the line for Current fiscal year. I would like to end this line after the last completed month, but I do not know how. Can you help me? See my formula below!
My result now is shown here:

My desired result should be:

Formula Current Fiscal Year:

Current Fiscal Year Cumulative Sales for completed months =
CALCULATE(
SUM(Append3[EUR Sales]),
FILTER(
ALLSELECTED('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = 0 &&
'Calendar Posting Date'[FiscalMonthNum] <= MAX('Calendar Posting Date'[FiscalMonthNum]) &&
'Calendar Posting Date'[CurrentDayOffset] < 0 &&
'Calendar Posting Date'[CurMonthOffset] <= -1
)
)

5 REPLIES 5
Helper III

Hi @FreemanZ
This formula gives the following error: Too few arguments were passed to the IF function. The minimum argument count for the function is 2.

Super User

Sorry, try like:

Measure =
IF(
MAX('Calendar Posting Date'[Date])<=MAX(Append3[Date]),
[Current Fiscal Year Cumulative Sales for completed months]
)

forgot to close the bracket.

Helper III

@FreemanZ
This is the result of this formula. Not what I was expecting.

Helper III

@FreemanZ
I have also tried the following formula:

FYTD Sales =
VAR CurMonthOffset = MAX('Calendar Posting Date'[CurMonthOffset])
RETURN
CALCULATE(
IF(CurMonthOffset > -1, BLANK(), SUM(Append3[EUR Sales])),
FILTER(
ALLSELECTED('Calendar Posting Date'),
'Calendar Posting Date'[CurFiscalYearOffset] = 0 &&
'Calendar Posting Date'[FiscalMonthNum] <= MAX('Calendar Posting Date'[FiscalMonthNum]) &&
'Calendar Posting Date'[CurrentDayOffset] < 0 &&
IF('Calendar Posting Date'[CurMonthOffset] > -1, BLANK(), 'Calendar Posting Date'[CurMonthOffset] <= -1)
)
)

The result will only show the line for January and February and not the first 3 month of the Fiscal Year.

Super User

hi @JC2022

try to plot a measure like this instead:

``````Measure =
IF(
MAX('Calendar Posting Date'[Date]<=MAX(Append3[Date]),
[Current Fiscal Year Cumulative Sales for completed months]
)``````