Forum Discussion

lordsriram's avatar
lordsriram
Frequent Visitor
7 years ago
Solved

SWITCH OR IF STATEMENT for PY SHIPPED AMOUNT

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(...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

zoloturu's avatar
zoloturu
Icon for Memorable Member rankMemorable Member
7 years ago

lordsriram,

 

Please post:

 

1. Sample data.

2. Explanation of a logic of these measures. 

 

Regards,
Ruslan

lordsriram's avatar
lordsriram
Frequent Visitor
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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