Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Using SAMEPERIODLASTYEAR and a FILTER together

I am displaying sales by product.  There is also the capability to filter by the site that the order comes from.  The logic is more complicated than can be achieved with a simple filter, so a separate measure is written for each site.  I am having problems with calculating last year's sales using SAMEPERIODLASTYEAR.  Here are three attempts for the site with index 1:

(DeviceMovement is my Fact Table.  Devices is a Dimension Table (1-1 within my data).  Calendar is a table marked as a date table. Country is a table with no relationships, providing logic for filtering each measure)

 

 

PY1simp = CALCULATE(COUNTROWS(DeviceMovement), FILTER(DeviceMovement, DeviceMovement[SENTFROMSITEID] IN {10, 43}), SAMEPERIODLASTYEAR('Calendar'[Date])) + 
CALCULATE(COUNTROWS(DeviceMovement), FILTER(DeviceMovement, DeviceMovement[SENTFROMSITEID] IN {7, 35}), FILTER(Devices, LEFT(Devices[DispatchSiteInvoiceNumber], 3) = "32-"), SAMEPERIODLASTYEAR('Calendar'[Date]))

 

 

Result = blank

 

 

PY1rem = CALCULATE(COUNTROWS(DeviceMovement), REMOVEFILTERS(DeviceMovement[Accounting Date]), FILTER(DeviceMovement, DeviceMovement[SENTFROMSITEID] IN {10, 43}), SAMEPERIODLASTYEAR('Calendar'[Date])) + 
CALCULATE(COUNTROWS(DeviceMovement), REMOVEFILTERS(DeviceMovement[Accounting Date]), FILTER(DeviceMovement, DeviceMovement[SENTFROMSITEID] IN {7, 35}), FILTER(Devices, LEFT(Devices[DispatchSiteInvoiceNumber], 3) = "32-"), SAMEPERIODLASTYEAR('Calendar'[Date])

 

 

Result = blank

 

 

PY1all = CALCULATE(COUNTROWS(DeviceMovement), FILTER(ALL(DeviceMovement), DeviceMovement[SENTFROMSITEID] IN {10, 43}), SAMEPERIODLASTYEAR('Calendar'[Date])) + 
CALCULATE(COUNTROWS(DeviceMovement), FILTER(ALL(DeviceMovement), DeviceMovement[SENTFROMSITEID] IN {7, 35}), FILTER(Devices, LEFT(Devices[DispatchSiteInvoiceNumber], 3) = "32-"), SAMEPERIODLASTYEAR('Calendar'[Date]))

 

 

Returns a result but it is insensitive to the product types in my visual, and gives the same result for all of them.

 

How do I compose SAMEPERIODLASTYEAR with a filter to get the result I want?

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

     

    You can check the following:

    1. Whether your calendar table has a correct relationship with the main table.

    2. If the general result is empty, it is an error in the Filter function, which causes the data obtained by the context to be empty, and the virtual table formed by the Filter is empty. Check whether the filtering conditions inside are correct.

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for your reply, 

       

      Unfortunately my fact table has some columns calculated in power query, and the calculation references two sensitive tables.  It would be a bit messy to clean and keep valid data.

       

      Let me remove some confounding things

       

      Orders1 = 

      CALCULATE(COUNTROWS(DeviceMovement), [FILTERS]) +
      CALCULATE(COUNTROWS(DeviceMovement), [FILTERS])

      gives correct sales figures for date

       

      PY1simp =

      CALCULATE(COUNTROWS(DeviceMovement), [FILTERS], SAMEPERIODLASTYEAR('Calendar'[Date])) +
      CALCULATE(COUNTROWS(DeviceMovement), [FILTERS], SAMEPERIODLASTYEAR('Calendar'[Date]))

      blank

       

      PYcalc = 

      CALCULATE([Orders1], SAMEPERIODLASTYEAR('Calendar'[Date]))

      previous year figures as expected

       

      So the date table is complete for the period I was looking at, at least (it's constructed using {Number.From()..Number.From()} then converted type).  The [FILTERS] do not contain anything time related.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I've managed to use 

    PY1 = CALCULATE([ThisYearsOrders1], SAMEPERIODLASTYEAR('Calendar'[Date]))

    I had previously tried to PYAll = CALCULATE([AllOrders], SAMEPERIODLASTYEAR('Calendar'[Date]))
    and then filtered it using the same site-specific filters as in the [ThisYearsOrdersx] type measures and that failed.

    My question is now how can I correct the measures from the attempt I gave in the question if I wanted to do it that way, and why does the two step calculation work one way and not the other?

     

    Thanks