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 have a list where i keep track of the total number of hours worked in a company per month. I need to get a sum of the last 12 months on each row. This is what i would want:
Date Total Hours Cumulated hours
1/1/2014 5000 5000
1/2/2014 6000 11000
..
1/12/2014 8000 185000 (total of 1/1/2014 till 1/12/2014)
1/1/2015 7000 187000 (total of 1/2/2014 till 1/1/2015)
I don't really know if I should do this in a column or a Measure. This is what i have so far: Calculate(Sum('Table'[Total Hours]),DATEADD('Table'[Date],-1,YEAR)<'Table'[Date])). But this is not working. Can someone push me a bit in the right direction please?
Solved! Go to Solution.
You could try something like this:
CALCULATE( SUM( 'Table'[Total Hours] ), DATESBETWEEN( 'DateTable'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ), LASTDATE ( 'DateTable'[Date] ) ) )
You could try something like this:
CALCULATE( SUM( 'Table'[Total Hours] ), DATESBETWEEN( 'DateTable'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ), LASTDATE ( 'DateTable'[Date] ) ) )
Sdjensen,
I tried this but this gives me just the same value as the column total hours.
Do you have a seperate table with all the dates from the first date in your data to the last and made a relationship between these 2 tables?
Thanks Sdjensen,
I forgot to make a connection between the 2 tables. It works now.
Hi
Try this
Last6SalesNew:=IF(ISBLANK(sum(SalesData[SalesAmount])),BLANK(),
CALCULATE(sum(SalesData[SalesAmount]),
DATESBETWEEN(
Calendar[FullDate],
FIRSTDATE(PARALLELPERIOD(Calendar[FullDate], -6, MONTH)),
LASTDATE(PARALLELPERIOD(Calendar[FullDate], -1, MONTH))
), ALL(Calendar) ))
This gives me the sales for the previous six months from current month displayed in the pivot table or charts.
Replace the formulas with the columns in your data model.
This assumes a date table called as Calendar and is linked to the SalesData date field.
Best
Cheenusing
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |