PTD Last year based on a slicer that point to a certain date today (trying to get partial PTD LY)

[Anonymous]

The formula below capture accurately the whole, full period of the previous year.

However,

I am trying to capture the sum of the prior period (PTD) based on licer that points to a specific date today.  (other words, trying to get partial PTD).

example: if date slicer points to 12/10/2023 then I am trying to get total of 12/1/22 thru 12/10/2022 (PreviousYear DAX formulas won't work here, because our calendar is non-fiscal)

PTD_PY =

VAR __Maxdate= Max(calendar_mapping[Date])

VAR __Period= MAX (calendar_mapping[Period])

VAR __PrevYear= YEAR(__MaxDate)-1

VAR __Table=FILTER(ALL(calendar_mapping),[Period]=__Period && YEAR([DATE])=__PrevYear &&[Date]<=__Maxdate)

VAR __PTDLY=SUMX(__Table,[Net Sales])

RETURN

__PTDLY

[Fowmy]

@Anonymous 
Please share a PBIX file with your custom date table and a dummy fact table. You can share it through Google Drive or One Drive link here. 

[Anonymous]

https://www.dropbox.com/scl/fi/nvbxbgm4vdv8ftnu3i7ep/DUMMY_test.pbix?rlkey=acb99llc0k0fjlyynji15ssh1...

Hello Fowmy,

please let me know if you can open the link.