Measure Calculate YTD versus Last YTD

[EFA10]

HI,

I have created a formula in my Power Query & Data Model (With Calendar), to compare the sales for 2020 (till end of June) and the same result for 2019/2018 (end of june).
Formula is "CALCULATE'('DATA[Total Invoice];SAMEPERIODLASTYEAR('Calender[Date])
Result is correct when I am looking in details, on 30/06/2019 I have the correct amount but the final result in the Measure*is the Total of 31/12/2019 and not at the same period for 2020 > 30/06/2020.

How can I have the total for the same period > end of invoicing of 2020, now is 30/06 but next month it will be 31/07...
I don't want to use the filter by a slicer, I would like like to have the formula independant of the slicer.

Thanks for your help.
Nice Weekend.
Eric

[oeldere]

Make a column in the original data and use a if statement to refer to that criteria ("yes"). After that you can make a pivot table and add the criteria in the filter box and select on "yes".

[EFA10]

Hi,

Thank You for your advice.
Can you explain with an example or the formula for the statement.
Many thanks.

Eric

[oeldere]

You want help, so I expect you could make an example that shows your problem. Please add also manualy the expected result.

[EFA10]

See below, POST resolved

[JeteMc]

Please utilize the instructions in the banner at the top of the page to upload the .xlsx file from which the screen shot in post #5 is taken.
Let us know if you have any questions.

[EFA10]

Hi,

Here is my example with the problem of LYTD.
Thanks.

Eric

[JeteMc]

Maybe not the most elegant of solutions, however have you considered adding a timeline?
Pivot Table Tools > Analyze > Insert Timeline
Select January 2018 through June 2020.
Let us know if you have any questions.

Edit: there is also a section, "USING THE DATA MODEL", of the article linked below which may be helpful.
https://sfmagazine.com/post-entry/ap...a-pivot-table/

[EFA10]

Hi,

Yes we can use the timeline but this is not a solution for me.
Kind regards,

Eric

[JeteMc]

Here is a proposal based on oeldere's suggestion, except instead of adding the column to the source data it is added to the calendar.
The column header is Include and the formula is: =AND(MIN(Table1[Date])<='Calendar'[Date],MAX(Table1[Date])>='Calendar'[Date])
Include is then used as the filter for the pivot table.
Let us know if you have any questions.

[EFA10]

Thank You, it is working fine.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.