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( '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

Need Help

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

3 Replies

zoloturu
Memorable Member
7 years ago
lordsriram,

Please post:

1. Sample data.
2. Explanation of a logic of these measures.

Regards,
Ruslan
- 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
    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

Forum Discussion

SWITCH OR IF STATEMENT for PY SHIPPED AMOUNT