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.
Hi all,
I'm trying to measure an cumulative sum over time that only takes data within a year.
From this post, i got this code:
Measure =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Date] <= MAX ( DateHelper[Date] ) )
)
But i would like to SUM the Table1[Amont] considering only the data within the last year (Date - 365 days). Can someone help me on this subject?
Solved! Go to Solution.
Hi,
Create a Calendar Table and build a relationship from the Date column of Table1 to the Date column of the Calendar Table. In the Calendar Table, create this calculated column formula to extract the Year: Year = Year(Calendar[Date]). To your slicer/visual/filter, drag Year from the Calendar Table. Write these measures
Total amount = SUM ( Table1[Amount] )
Total amount last year = CALCULATE([Total amount],PREVIOUSYEAR(Calendar[Date]))
Hope this helps.
Hi,
Create a Calendar Table and build a relationship from the Date column of Table1 to the Date column of the Calendar Table. In the Calendar Table, create this calculated column formula to extract the Year: Year = Year(Calendar[Date]). To your slicer/visual/filter, drag Year from the Calendar Table. Write these measures
Total amount = SUM ( Table1[Amount] )
Total amount last year = CALCULATE([Total amount],PREVIOUSYEAR(Calendar[Date]))
Hope this helps.
Hi @Anonymous ,
You could replace MAX ( DateHelper[Date] ) with (MAX ( DateHelper[Date] )-365) in your filter formula.
Or you could use SAMEPERIODLASTYEAR() function to get data of previous year.
Hi Hsneto - you want to try something like this.....
Sales Total =
CALCULATE (
[measure],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
)
Cheers
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |