Display a running total by category in an overlay that ignores the year?

[ahumke]

I'm having problems displaying a running total that will compare the applications received count by month and month/day (at a drill down) and by application intended start term. I was trying to constrict the applications to display from October through September - so whatever count was there at the begining of October it would start there and then count up through what it is at the end of September.  And also display an as an overlay (so the year won't display) so we know how we compare to that month/day when we were accepting applicants for the prior year fall term. I've uploaded some dummy data of an attempt but I can't get the date display to start in October as intended and it only seems to work for the most recent term - all other terms display the application count at the term's completion (straight lines at the top)

Power BI file:

https://mchenrycountycollege-my.sharepoint.com/:u:/g/personal/ahumke_mchenry_edu/Eed5QPVBQyFDhF9_sh1...

CSV Data File:

https://mchenrycountycollege-my.sharepoint.com/:x:/g/personal/ahumke_mchenry_edu/EbfwdlYESrlJr34Byyd...

[v-shex-msft]

Hi @ahumke ,

Based on the test, I found this issue should be related to your formula and chart axis.

In your fact table and calendar table, they not contain records after September 2019, when you max function to get the current date from the line chart axis group, it extract existed last date of the current month.

For instance, when measure calculate on 'September', it will get September 2019 and rolling records who less than or equal to September 2019;
when measure formula calculates in 'October' category, it can only get October 2018 and will rolling records who less or equal to October 2018. (2019 records has been ignored in this calculation)

For your scenario, you can take a look at the following methods to fix it.

1. Add missed records of records to the fact table. (records of October, November, December 2019 with count '0')

2. Expand your calendar table to store whole year date.

Regards,

Xiaoxin Sheng

[ahumke]

Thank you for looking at my problem! I changed the date table so it will fill in the dates for the complete fiscal year (ending in September) and I updated the linked file. But I don't think that really solved the problems. I still don't know how to 1) get the line chart to start in October and not January, 2) get the older year to calculate and display the running total as it accumulated day by day or month by month through the year.  I agree with you the axis of the chart is a problem because the data want to be referencing the year it was collected but I don't know how to take that out but still see the day by day running totals.  Is there a way to make this work?

[v-shex-msft]

Hi @ahumke ,

#1, You can create a custom sort order table with custom sort order and link to original table fields, then you can use new table fields to replace original files to achieve custom sort order.

Custom Sorting in Power BI

#2, Yes, this issue will appear when you use sub-level fields on the 'axis' field.  The workaround is to use other date fields which has full date records to loop, then you can use Dax formula to get the current date(from full date table) as filter condition to calcautle.

Regards,

Xiaoxin Sheng

[ahumke]

I think I can follow your instructions on the sort table. I do have a sort column in the current table but that isn't working so hopefully taking it out to its own table makes a difference. I don't understand your suggestion for the second part. Can you provide more detail? I have experimented with fields that carry the month and the day without the year but it does not display correctly.

Thanks!