Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |