SWITCH OR IF STATEMENT for PY SHIPPED AMOUNT

[lordsriram]

I am trying to get the following in a calculated measure value

When the Year is current year - I want the calculation to be

CALCULATE(SUM('TRDTA PWRBI_SALES'[SHIPPED AMOUNT]),DATESBETWEEN( 'INVOICE DATE'[INVOICE DATE].[Date],DATE (YEAR(TODAY())-1, "1", "1"),DATE (YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY())))

 

else I want the calculation to be 

CALCULATE(SUM('TRDTA PWRBI_SALES'[SHIPPED AMOUNT]), SAMEPERIODLASTYEAR('INVOICE DATE'[INVOICE DATE].[Date]))

When it is current year, I want to compare only till date and other years, I want to use whole year. 

Please let me know if there is a better way to do this

Thanks

Sriram

[zoloturu]

@lordsriram,

Please post:

1. Sample data.

2. Explanation of a logic of these measures. 

Regards,
Ruslan

[lordsriram]

@zoloturu

sample data

Attached link has the file with the sample data

I am trying to get the total of shipped amount for each year and for the current year - I want the Previous year sales to be only for the dates till current date. For the rest of the years, i want to use the whole year for calculation. I want this to be in PY Shipped amount field and was trying to change the calculation display based on the year from invoice date table. I can do this in 2 different measures but the display to the user is a bit confusing.

Thanks

Sriram

[v-shex-msft · Accepted solution]

HI @lordsriram,

If you mean a measure contains two calculate logic based on 'today' function, you can try to use below measure formula:

Dyanmic Measure = 
VAR currDate =
    MAX ( Invoice[INVOICE DATE] )
RETURN
    IF (
        YEAR ( currDate ) = YEAR ( TODAY () ),
        CALCULATE (
            SUM ( 'SALES'[SHIPPED_AMOUNT] ),
            FILTER (
                ALLSELECTED ( Sales ),
                [INVOICE_DATE]
                    >= DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
                    && [INVOICE_DATE]
                        <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
            )
        ),
        CALCULATE (
            SUM ( 'Sales'[SHIPPED_AMOUNT] ),
            FILTER (
                ALLSELECTED ( 'Sales' ),
                YEAR ( [INVOICE_DATE] )
                    = YEAR ( currDate ) - 1
            )
        )
    )

Regards,
Xiaoxin Sheng