Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JC2022
Helper III
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:

JC2022_0-1680256294146.png

 

My desired result should be:

JC2022_1-1680256651945.png

 

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
JC2022
Helper III
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.

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. 

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

JC2022_0-1680271964610.png

 

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

JC2022_1-1680274165932.png

 

FreemanZ
Super User
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]
)

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.