Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 |
Solved! Go to Solution.
@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())) )
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
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
Hi,
Does this measure work?
Collections LY by trend date = if(min('Calendar'[Date])<=TODAY(),CALCULATE([Collections Total],SAMEPERIODLASTYEAR('Calendar'[Date])),blank())
@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())) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |