Get trends with 2 different dates in a single chart

[itsyash007]

I have data for multiple tickets in an excel tracker. I want to represent two trends on a chart in PowerBI.

First one is 'Received Volume' depending on the 'Received Date'

Second one is 'Completed Volume' depending on the 'Completion Date' and 'Status'

All these fields are in the same table.

I was able to create two separate charts but my challenges in getting both these together as values in the axis will be based on different columns.

Probable solution is that I create a new table which has 'Date', 'Count of Received Volume based on 'Received Date' and 'Count of Completion Volume, based on 'Status' and 'Completion Date'

I was able to create a new table with unique dates using 'Calendar' DAX function.

However, I am not able to crack the calculation of received volume and completion volume (similar to countif in excel). I believe I am struggling because PowerBI is calculating at column level and returning the same value irrespective of the date.

Format in which I am expecting my new table:

Date | Received Count | Completed Count

Any help is truely appreciated.

Thanks,

Yash

[Ashish_Mathur]

Hi,

Along with the dataset that you share, also show the expected result.

[davehus]

Hi, Do you have an anonymised sample of your data that you could upload?

[itsyash007]

Hi, Below is my existing table and the output that I am expecting. I am able to get the unique dates in the new table but not able to get the tickets received and completed. Note: For tickets completed, I need to consider 'Date completed' with status as 'Completed.

[Ashish_Mathur · Accepted solution]

Hi @itsyash007,

You may download my solution from here.

Hope this helps.