Cumulative Sum by Date w/ other Filter(s)

[MrSaltyAlt]

So I'm an intern with a 3PL doing Project Management/ PMO responsibilities. My job has tasked me with creating a Run rate overall, also showing the resolution of project tickets (Cancelled vs. Completed) submitted in the calendar year. They also want to be able to filter it by department/program. 

I cannot figure out how to 1) get the actual date (day and month) to show on a line graph, it just gives a date count (like 1-50 so to speak fo how many days, doesnt give a date) and 2) how to get the cumulative part of all this to show. I've tried a few formulas I've seen online but my data never ends up looking similar/usable. Is my data not organized in the right way? Do I need to make relationships for this to be possible? 

I have a very introductory understanding of PBI from coursework, but have never done anything close to this.

I am lost.... please help

[Anonymous · Accepted solution]

Hi  @MrSaltyAlt ,

I created some data:

Here are the steps you can follow：

You want to show the actual date, you can click on X axis -- Type – Categorical

You want to display the cumulative sum:

1. Create measure.

Flag =
var _select=SELECTCOLUMNS('Table',"1",'Table'[department])
return
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[department] in _select))

2. Result:

If you need pbix, please click here.

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Whitewater100]

Hi:

It sounds like you need a couple(at least) unique dimension tables for Department and program. Also critical is a date table. This Date Table needs to be marked as a date table and have relationship with your fact table. Dimension tables connect to main fact tables. I will include an example file with Fact & Dimension Tables with relationships.and Date Table Code. To get a date table Modeling>New Table.

https://drive.google.com/file/d/1QgEO4YwQQh2lqtXk1hnwQYVBuv6BbKcS/view?usp=sharing 

Sounds like you will then need measures for cancelled and completed tickets.

e.g. my guess...Cancelled Tickets = CALCULATE(SUM(FactableTickets]), FILTER(Facttable, Factable[Ticket Status] = "Cancelled")

same for the Completed.

You could have a measure for Total tickets too.

When you do the visuals only use columns from your dimension tables(like date or year). Then youe measures should work well.

If you get this started I'm happy to check it out. Some people like using example data so nothing secrete is revealed.

I hope this helps.