Forum Discussion
Getting Period values from Cumulative
Hi team,
I am new to the community and learning Power BI. I have imported the Excel weekly spread data into Power BI and unpivoted. I need to get period values (weekly quantity) from the available cumulative values. Could anybody guide me?
i can't open the link you provided.
try below coding.
Measure =var _date=maxx(FILTER(all('Table'),'Table'[CutoffDate]<max('Table'[CutoffDate])),'Table'[CutoffDate])var _value=sum('Table'[CumCablePull])-sumx(FILTER(all('Table'),'Table'[CutoffDate]=_date),'Table'[CumCablePull])return if(not(hasonevalue('Table'[CutoffDate])),sum('Table'[CumCablePull]), if (_value<0,0,_value))
14 Replies
Assuming your table is called "Table" and the columns are: "CutoffDate", "CumCablePull".
Create a calculated column:
Weekly Qty = VAR PrevValue = CALCULATE( MAX('Table'[CumCablePull]), FILTER( 'Table', 'Table'[CutoffDate] = EARLIER('Table'[CutoffDate]) - 7 ) ) RETURN 'Table'[CumCablePull] - COALESCE(PrevValue, 0)if you want to create a measure, you can try this
Measure =var _date=maxx(FILTER(all('Table'),'Table'[CutoffDate]<max('Table'[CutoffDate])),'Table'[CutoffDate])return sum('Table'[CumCablePull])-sumx(FILTER(all('Table'),'Table'[CutoffDate]=_date),'Table'[CumCablePull])pls see the attachment below
Hi,
If you want a measure solution, then try this approach
- Create a Calendar table and build a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar table
- To your visual, drag Date from the Calendar table
- Write these measures
Total = sum(Data[Sales])
Total a week ago = calculate([Total],datesbetween(calendar[date],min(calendar[date])-7,min(calendar[date])-7))
Total for the week = [Total]-[Total a week ago]
Hope this helps.
ryan_mayu Thanks, this is close to the requirement except want all positive values only so that period values will be used for hitogram and cumulative values will be used for curve. How to avoid the negative value at 2027/1/22?
what do you want to display if it's a negative value? shows 0?
then you can try this
Measure =var _date=maxx(FILTER(all('Table'),'Table'[CutoffDate]<max('Table'[CutoffDate])),'Table'[CutoffDate])
var _value=sum('Table'[CumCablePull])-sumx(FILTER(all('Table'),'Table'[CutoffDate]=_date),'Table'[CumCablePull])return if (_value<0,0,_value)Hi ryan_mayu and Ashish_Mathur , the below is snap after applying both advice
The all period values shall total up to the last cumulative value of 1254234, but the Measure total gives 34,305,799
The calendar table method Thisweek Qty gives cumulative values, not the period values.
Am I making any mistake in DAX? Sorry I dont know how to attach my .pbix file.
