Combine two clustered charts

[Sean2]

Not having any luck finding help on this one but I can't be the first to ask. I have two visuals that I'd like to have in one. I have a Date Table, and I have another CSV data source with labor hours by day.

I've got it looking mostly how I'd like but it would be nice to have them to together. I have little to no experince with DAX.

Thanks!

[v-eqin-msft]

Hi @Sean2 ,

As I mentioned before :drag Date.Year to Legend field to get values by different years

1. Apply filter—— Year=2020 or 2021:

2. Add a slicer:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sean2]

Thanks Evelyn. This is the piece I was missing. Using that legend trick will be very helpful going forward!

[v-eqin-msft · Accepted solution]

Hi @Sean2 ,

1.Did you apply some filters like Year=2020/ Year=2021 to get such output ?

2. If your data from CSV file has Date column,  and you have used it for X-axis, could you please drag Date.Year to Legend field to get values by different years? 

3. And what is the Date table used for?  Is there ny relationship between these two tables?

4. Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sean2]

Hi @v-eqin-msft , thanks for taking the time to look. 

Yes I just applied a filter to each of those visuals. The Date table is related.

I have posedt a PBIX file with other stuff removed. If you could take a look I'd really appreciate it.

https://drive.google.com/drive/folders/1nByRHwkPuWEOtKXzV84tpOHAin5idOHQ?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sean2]

Thanks for showing me the "same period last year" that could be helpful too.

[Ashish_Mathur]

Hi,

Ensure there is a relationship from the Date column of your Data table to the Date column of the Date Table.  In the Date Table, write calculated column formulas to extract Year, Month Name and Month Number.  Sort the Month Name by the Month number.  To your visual, drag Year and Month Name from the Date Table.  Write this measure

Total hours = sum(Data[hours])

Hope this helps. 

[Sean2]

Thanks @Ashish_Mathur 

How would I create the custom columns for 2020 and 2021? Can't figure out how to write that.

[Ashish_Mathur]

Hi,

If you want to create a Calendar Table for 2020 and 2021, then simply write this Table formula

Calendar = calendar(date(2020,1,1),date(2021,12,31))

Now add as many calculated columns as you want to this Calendar Table.

[amitchandak]

@Sean2 , Create measure for this year and last year and use in same visual

create a date table join on date, have year and month in date table

Option one Year as legend , Month on axis and measure on values 

In case you need measure , then for last year use measure like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Sean2]

Thanks @amitchandak. Thanks helps some, but I'm wanting a measure for specific years. Not relative to current year.

Is there a way to make that kind of measuer for 2019, 2020, 2021, ect?