cancel
Showing results for
Did you mean:
Frequent Visitor

PLZ HLP!!!! Creating calculation for Comparing Same Date This Year versus Last Year

I am trying to do Time Series calculations (whatever that really means) for Same Date compared to Same Date Last Year. If you can kindly help me from  losing more hair I would kindly appreciate your generosity!

Here are the formulas I am using

• Collections LY by trend date = CALCULATE([Collections Total],SAMEPERIODLASTYEAR(('Calendar'[Date]))) - This is still showing dates until end of year.  I only want it to show data up until this years date last year.
• Collections TY by trend date = CALCULATE([Collections Total], FILTER('Calendar', 'Calendar'[Date] < TODAY()))

 Date Collections TY by trend date Collections LY by trend date 11/30/2021 0:00 \$34,776.25 11/28/2021 0:00 \$852.91 11/27/2021 0:00 \$31,860.07 11/26/2021 0:00 \$207.88 11/25/2021 0:00 \$52,373.37 11/24/2021 0:00 \$14,278.67 11/23/2021 0:00 \$31,774.04 11/22/2021 0:00 \$24,062 11/20/2021 0:00 \$69,379.20 11/19/2021 0:00 \$12,817.15 \$32,289.16 11/18/2021 0:00 \$11,018.94 \$16,537.48 11/17/2021 0:00 \$21,989.74 \$30,357.17 11/16/2021 0:00 \$37,128.55 \$72,194.82 11/15/2021 0:00 \$6,564.91 11/13/2021 0:00 \$25,637.27 11/12/2021 0:00 \$17,404.07 \$11,558.63 11/11/2021 0:00 \$15,845.59 \$18,377 11/10/2021 0:00 \$14,042.51 \$34,213.14 11/9/2021 0:00 \$11,786.98 \$36,534.50 11/8/2021 0:00 \$19,856 \$0 11/6/2021 0:00 \$35,570.67 11/5/2021 0:00 \$17,466.67 \$19,411.81 11/4/2021 0:00 \$30,881.84 \$15,212.39 11/3/2021 0:00 \$13,875.26 \$32,911.77 11/2/2021 0:00 \$7,060.54 \$5,371.41 11/1/2021 0:00 \$13,559.69

2 ACCEPTED SOLUTIONS
Super User

@TheSweeper , In the case of the calculation group using tabular editor

CALCULATE(selectedmeasure(), FILTER('Calendar', 'Calendar'[Date] < TODAY()))

Last year

CALCULATE(selectedmeasure(), sameperiodlastyear('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today()))))

or

CALCULATE(selectedmeasure(), dateadd('Calendar'[Date],-1, year), 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today())) )

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Community Support

Hi  @TheSweeper ,

I created some data:

Here are the steps you can follow：

1. Create a calendar table.

``Slice = CALENDARAUTO()``

2. Create measure.

``````Collections LY by trend date =
CALCULATE(SUM('Calendar'[Collections Total]),
FILTER(ALL('Calendar'),'Calendar'[Date]=DATE(YEAR(MAX('Calendar'[Date]))-1,MONTH(MAX('Calendar'[Date])),DAY(MAX('Calendar'[Date])))))``````
``````Flag =
IF(MAX('Calendar'[Date])>=MIN('Slice'[Date])&&MAX('Calendar'[Date])<=MAX('Slice'[Date]),1,0)``````

3. Place Measure[Flag] in the filter, select is=1, and apply filter.

4. Result:

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

3 REPLIES 3
Community Support

Hi  @TheSweeper ,

I created some data:

Here are the steps you can follow：

1. Create a calendar table.

``Slice = CALENDARAUTO()``

2. Create measure.

``````Collections LY by trend date =
CALCULATE(SUM('Calendar'[Collections Total]),
FILTER(ALL('Calendar'),'Calendar'[Date]=DATE(YEAR(MAX('Calendar'[Date]))-1,MONTH(MAX('Calendar'[Date])),DAY(MAX('Calendar'[Date])))))``````
``````Flag =
IF(MAX('Calendar'[Date])>=MIN('Slice'[Date])&&MAX('Calendar'[Date])<=MAX('Slice'[Date]),1,0)``````

3. Place Measure[Flag] in the filter, select is=1, and apply filter.

4. Result:

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

Super User

Hi,

Does this measure work?

Collections LY by trend date = if(min('Calendar'[Date])<=TODAY(),CALCULATE([Collections Total],SAMEPERIODLASTYEAR('Calendar'[Date])),blank())

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@TheSweeper , In the case of the calculation group using tabular editor

CALCULATE(selectedmeasure(), FILTER('Calendar', 'Calendar'[Date] < TODAY()))

Last year

CALCULATE(selectedmeasure(), sameperiodlastyear('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today()))))

or

CALCULATE(selectedmeasure(), dateadd('Calendar'[Date],-1, year), 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today())) )

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements