Power BI running total from PWA data

[Anonymous]

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

[Anonymous]

Hi,

The issue there is that I am dealing with data that comes from the Project Web Application (PWA).

The adat set is huge even for the small amount of projects I currently have in there.

The expected result would be a month by month running total of the project cost...

[Ashish_Mathur]

Hi,

This pattern should work.

1. Create a Calendar Table and write calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To yoru visual, drag Project, Year and Month name from the Calendar Table
4. 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.

[Anonymous]

Thanks I will give that a shot.

As my daata already has Time by day in the table could I use that perhaps or am i trying to overcomplicate this...?

[Ashish_Mathur]

You are welcome.  You will need a proper date column (without a time stamp)

[Ashish_Mathur]

Hi,

Share some data in a format that can be pasted in an MS Excel file and show the expected result very clearly.