Use SAMEPERIODLASTYEAR() with another year-month slicer

[yyp]

Hi ,

We are working on a dataset as below, the goal is to compare current date inventory vs same date last year inventory for a given data_slicer value.

This data model is set as direct lake mode in Fabric, so we use calculation groups to create measures and CY/LY functions.

tot_inventory = sum('sample'[Inventory])

inventory = [tot_inventory]

cy_inv = CALCULATE(SELECTEDMEASURE())

ly_inv = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(calendar_date[calendar_date]))

It works at total level, but when Date_slicer is applied, it only returns corresponding result. The question is, how to include date_slicer in the calculation for cy_inv and ly_inv. For example when 202508 is selected , then cy_inv is 2705 and ly_inv is 2468 and they are both shown.

 Any suggestions? Thanks a lot in advance!

YYP

[yyp]

Hi @lbendlin Thanks a lot for your reply! In this sample file "Date1" has only one date but in reality this field will be contigous date values - like daily snapshots which will be used as x-axis in a line chart to show trended view. There is a one to many relationship between "calendar_date[calendar_date]" and "Date1", as I have more than one fact tables so CY/LY metrics are calculated referring to  "calendar_date[calendar_date]". The  "Date_Slicer" column will be only used as slicer, so by selecting specific year-month(s), we can compare inventory of that month(the next 5th month from now for example) of last 10 snapshot dates, versus last year same period. 

Thanks!

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[yyp]

@lbendlin Thanks I prepared sample file. Can I use google drive to share? Dropbox is blocked by company for security. Any other tool I can leverage. Sorry for the basic questions I am new to the forum.

[lbendlin]

yes, Google Drive will work.

[lbendlin]

SAMEPERIODLASTYEAR works across contigous date values. You seem to be trying to apply it to some other value "Date_Slicer"  which is not a date.  Create another column that represents Date_Slicer as a true date (for example "2024-08-01"  and join that to the calendar table instead of the "Date1"  column.