topic Re: How to stop a line in DAX Commands and Tips

How to stop a line

JC2022 — Fri, 31 Mar 2023 09:59:51 GMT

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

)

Re: How to stop a line

FreemanZ — Fri, 31 Mar 2023 13:51:12 GMT

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

Re: How to stop a line

JC2022 — Fri, 31 Mar 2023 13:59:14 GMT

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.

Re: How to stop a line

FreemanZ — Fri, 31 Mar 2023 14:06:40 GMT

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.

Re: How to stop a line

JC2022 — Fri, 31 Mar 2023 14:12:58 GMT

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

Re: How to stop a line

JC2022 — Fri, 31 Mar 2023 14:49:33 GMT

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.