Stacked column chart with four £ columns that use 2 different date columns

[LambellaJules]

Hi folks

Sorry I seem to have posted without putting in context!

Power BI newby here with a chart question I cannot resolve.  

I have a table of data that has four £ columns and two date columns.  I need one £ column to be based on the first date column, the other three £ columns to be based on the second date column.  I need all the data to come from the single table as I have to enable drilldown to detail lists.

Any ideas?  I hope someone has a solution out there and looking forward to hearing from you.

Cheers

Jules

[v-juanli-msft]

Hi @LambellaJules 

If you'd like this transformation:

Original

Final result:

If so, please open Edit queries,

select "date1" and "date2", unpivot columns,

then select "value1","value2","value3","value4", unpivot columns,

remove "Attribute" columns, rename columns: "Attribute.1" to "value type", "Value" to "date", "Value.1" to "value"

Close&&apply

Add columns to a matrix visual where you could drill down of up.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LambellaJules]

Hi Maggie

Thanks for the suggestion, I'll see if I can follow your instruction.  It's actually a clustered column chart I need to reproduce (not stacked - as below) where my source is as you describe although there are a number of other columns in the data that I need to use in the drillthrough to 3 other lists on the report.  The data actually exists in two tables in dynamics crm and to achieve the chart at the moment I've appended the two tables together, renaming the date column in one so it matches the name in the other table so the chart is giving the correct information but that then doesn't allow me to do the drillthrough as I get two rows for each item.  If I merge the queries instead of append, which is the ideal, that would allow the drillthrough but won't allow me to select one Date1 for Value1 and Date2 for Values 2-4.  

I'll let you know how I get on!

Cheers

Jules

[LambellaJules]

Hi Maggie

Sorry not sure if I've done something wrong but I've still got two columns for the different date values - 'date' and 'value' - so I can still only use one of these on the axis for the chart.  Unpivoting the columns also reduces the dataset to those rows that contain any of the four values, i.e. removing the rows that do not have any financial data, which means I cannot drill through to all the source data.

Back to the drawing board :(.

I've spotted an obvious way the system should allow - once I've built the chart, right click on each of the value columns allows create quick measure, but I can't see anything in the 'select a calculation' list that allows sum by selected date.  Or perhaps two axis?  Just trying to throw ideas together and not getting anywhere.

Thanks again

Jules

[v-juanli-msft]

Hi @LambellaJules 

Is this your expected result?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LambellaJules · Accepted solution]

Hi Maggie

I finally managed to get it all working using a date table and USERELATIONSHIP.  Wasn't easy, took a long time as all the web posts I found weren't exactly clear but at least I got there in the end.

Thanks for your interest though, hopefully I'll get getting some PBI training soon so I can stop asking so many questions!

Cheers

Jules