Forum Discussion
Helper ICreating a Normalized Tabled with daily rows over the course of a year
Although I had a couple users help me out with this, no one really got me the solution I was looking for. I ended up sorting it out on my own as follows:
1. Manually (what I was trying to avoid) created a "Normalized" tab in my data source, excel and copy/pasted the data from each year into it's own column. Created a column along the left numbered 1 - 365 to represent the days of the year (there doesn't seem to be a date format allowing no year to be represented).
2. Within PowerBI generate a "Running total" measure using the category of interest and "Day of Year" which is what I'd called the numbered day column
Then you are able to plot multiple categories on one graph as follows:
Idealy I'd like to have the numbers at the bottom show up more like Jan 1, Feb 1, March 1, etc. but am not sure how. For the mean time this is a 90% solution to my original problem.
I would keep this table and set a visual filter for greater than 2015 year. To remove the year if the date in hierarchial click the down arrow on the field and click Date Hierarchy then clcik the x next to year. If not you may have to create a date table and relate it to your date field.
You will have to create a measure for each Column in your table.
For cumulative I'd use the following
CALCULATE(
SUM('Table'[Column]),
FILTER(
ALLSELECTED('Table'),Table'[Date Field] <=MAX(Table'[Date Field]))
If you want the cumulative vale to start over after a period add this at the end within calculate.
,VALUES('Table'[Date Field].[Year])) the Year can be Month if you want monthly cumulatives.
Helper IUltimately I want to overlay each year as its own line, colored by year, so I can visualizing the changing cumulatively expenses by day (X-axis) throughout the year.
So I think setting a visual filter won't allow me to display multiple years in this way?
I have created a table with just a column for day of year 1 - 365 but now want to have the cumulative columns based on each year and category relating to each day. I have posted an image below in a response to BILASolution of how I think the table should look to help me achieve visualizing multiple years normalized with a line for each year. Hopefully that makes sense.
I tried the formula here but it seems I'm getting just a general sum, I need to somehow filter it to just "DayNoOFYear" for the year I'm interested in, say 2015 or 2016 so that a given columns will only have "Groceries" cumulative sums for the day number of year but within 2015 alone, so I can do this for each year and then have unique lines for visualize.
Hopefully that makes sense, thanks for your help.
- BILASolution
Solution Specialist
Hi DataGeo
Check this pbix. Tell me if this is what you're looking for
https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dyKxTaxuA6pAP8A
BILASolution
- DataGeo
Hi Bila,
I think this visualization is close, but what I will need to do is extract data from the original table to create the "normalized table".
One difference is I want to have separate lines with colors by each year, see example here:
- BILASolution
