Forum Discussion
Power BI running total from PWA data
Hi Team,
I am trying to create a cashflow dashboard in Power BI for the total cost from a set of PWA data.
I have Power BI attached to the database and have created the below DAX code having followed some youtube tutorials:
Cumulative total =CALCULATE( AssignmentTimephasedDataSet[AssignmentCost], FILTER( all(AssignmentTimephasedDataSet[TimeByDay],) AssignmentTimephasedDataSet[TimeByDay] < max(AssignmentTimephasedDataSet[TimeByDay])))
Where AssignmentCost is the cost field, I have used this for a monthly cost and it works. Am i looking at this in the right way, help please nearly lost a whole day on this.
I have also tried to add a column to the dataset to do the running total using the code below and trying to point it at a specific project:
Rolling total cost = var CurrentDate = AssignmentTimephasedDataSet[TimeByDay] var FilteredTable = FILTER(AssignmentTimephasedDataSet,AssignmentTimephasedDataSet[TimeByDay]<=CurrentDate) && Projects[ProjectName]= "Nimbin Water Main Resintatement" return
Calculate(AssignmentTimephasedDataSet[AssignmentCost],FilteredTable)
Again no joy please help Thanks
5 Replies
- Ashish_Mathur
Super User
Hi,
Share some data in a format that can be pasted in an MS Excel file and show the expected result very clearly.
- Ashish_Mathur
Hi,
This pattern should work.
- Create a Calendar Table and write calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number
- Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
- To yoru visual, drag Project, Year and Month name from the Calendar Table
- Write these measures
Total = sum(Data[Total])
Total YTD = calculate([Total],datesytd(Calendar[Date],"31/12"))
The YTD calculation would restart in January each year.
Hope this helps.
