Forum Discussion

Waseem_Abbas's avatar
Waseem_Abbas
Regular Visitor
2 years ago

Comulative sales measure problem

I want to create a measure as under

 

Cumulative Sales 2023 = VAR CurrentYear = YEAR(MAX('Date Table'[Date])) VAR CurrentMonth = MONTH(MAX('Date Table'[Date])) VAR CurrentDate = DAY(MAX('Date Table'[Date]))

RETURN

IF( CurrentYear = 2023 && (YEAR('Date Table'[Date]) < 2024 || (YEAR('Date Table'[Date]) = 2024 && MONTH('Date Table'[Date]) = 1 && DAY('Date Table'[Date]) = 1)), CALCULATE( [Total net sales], FILTER( ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date]) && YEAR('Date Table'[Date]) = 2023 && (YEAR('Date Table'[Date]) < 2024 || (YEAR('Date Table'[Date]) = 2024 && MONTH('Date Table'[Date]) = 1 && DAY('Date Table'[Date]) = 1)) ) ), BLANK() )

 

in mention measure shows that the column name "Date" in not find in date table. but i have double check the name and data type of my date column. but it under line with red of every date column in mention in measure. what is the issue

General Comment

2 Replies

  • devesh_gupta's avatar
    devesh_gupta
    Impactful Individual
    2 years ago

    Waseem_Abbas Please make sure that you've marked your table as a date table. To do so, right-click the table's name in the Fields pane and select Mark as date table. Power BI verifies the data in the table by marking it as a date table, ensuring that the date column is of data type Date and contains unique values.

    If you find this insightful, please provide a Kudo and accept this as a solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Waseem_Abbas ,

    Please try:

    Cumulative Sales 2023 = 
VAR CurrentYear =
    YEAR ( MAX ( 'Date Table'[Date] ) )
VAR CurrentMonth =
    MONTH ( MAX ( 'Date Table'[Date] ) )
VAR CurrentDate =
    DAY ( MAX ( 'Date Table'[Date] ) )
RETURN
    IF (
        CurrentYear = 2023
            && (
                YEAR ( MAX('Date Table'[Date]) ) < 2024
                    || (
                        YEAR ( MAX('Date Table'[Date]) ) = 2024
                            && MONTH ( MAX('Date Table'[Date]) ) = 1
                            && DAY ( MAX('Date Table'[Date]) ) = 1
                    )
            ),
        CALCULATE (
            [Total net sales],
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
                    && YEAR ( 'Date Table'[Date] ) = 2023
                    && (
                        YEAR ( 'Date Table'[Date] ) < 2024
                            || (
                                YEAR ( 'Date Table'[Date] ) = 2024
                                    && MONTH ( 'Date Table'[Date] ) = 1
                                    && DAY ( 'Date Table'[Date] ) = 1
                            )
                    )
            )
        ),
        BLANK ()
    )

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum