Dynamic Days in Columns As well as Monthly Averages

Power BI
1

I am needing some help with a power bi issue.

I have a report layout that lists the day of the month in each column and then the last 3-month average.

However, the days are dynamic, so when it’s the current month, they only want to see that number of days reflected in the report.

I am not sure how to do this within Power BI. If I create a column for each day of the month, that would solve part of my issue, however, it’s not dynamic.

Example:

1 - Monday | 2- Tuesday | 3- Wednesday … | Last3Month Avg | YTD Avg

I am able to accomplish this within SSRS because it allows you to add rows as columns as well as additional columns.

Any help would be greatly appreciated.

2

Hi @ked05a,

May I see a picture of a dummy report? That way I can help you in a more effective way.

From what you are stating, probably the best course is to transform the report to a better form using the Query Editor, but I can’t affirm that until I see the report structure.

Best regards,

Jorge Galindo

3

Attached is the screenshot of an example.

Please note, this is built on an aggregation table where the underlying table contains about 40M. The aggregation table contains 4M. I have to have the ability to switch between months & years and this be dynamic.

Screenshot_1
Screenshot_1.png937×107 4.69 KB

Thanks,

4

Thanks for the example.

Just to confirm I got it right:

The top row are the month’s days and below is the day of the week of that day, correct?

Where can I check the month of this report?

Best regards,

Jorge Galindo

5

Yes, the day of Month & day of week is displayed in my example above.

The Month is simple a filter that you can select which month you would like to review.

Please note, the current month will only have the days up to the current day.

Let me know your thoughts, thanks.

–
K

6

Good day @ked05a,

I have made a very simple model you can apply to that table.

For the averages probably is better to use your underlying data, as well for building in a better way this model.

But what you should aim is for a model similar to the one I am posting here.

Data Model.xlsx (9.2 KB)
Data model.pbix (38.1 KB)

I think this Learning Summit will help you with your scenario:

Try doing it with your model and if you need further assistance, maybe you can share with me a pbix dummy file with you model and help you with it.

Best regards,

Jorge Galindo