cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jinny_le
Frequent Visitor

Interactive monthly & daily date visuals in the same dashboard

Hi everyone, 

I'm seeking some help regarding this issue on how to have both Monthly & daily visuals in the same dashboard. 

 

For example i have daily sales data by product, by country (image 1) and i want to have 2 visuals like in image 2. 

- 1st visual: is total sales 

- 2nd visual: monthly sales trend by product & month 

 

I've created: 

- Month column: Month = 'Table'[Date].[Month]

- Year column: Year = 'Table'[Date].[Year]

- Monthly sales measure: Monthly Sales = Calculate(sum('Table'[Sales ]),ALLEXCEPT('Table','Table'[Date],'Table'[Country],'Table'[Product]))

 

However, i still have 3 issues. I think the column & measure i created is wrong? 

- My year is cumulative (in bottom right table) 

- Monthly sales is the same across month (botton left chart)

- I cannot display month-year in the axit ie: Jan-2019, Jan-2020 

 

I have PBI file but not sure how to attach here. If this qs is not clear enough please let me know! 

 

sample1.png

 

sample 3.png

 

 

 

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

Hi @jinny_le ,

 

Please create a calculated column as below and add it to the x-axis to work on it.

 

Year Month = FORMAT([Date],"MMM-YYYY")

 

If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @v-frfei-msft 

Thanks you very much for the guidance below. I managed to solve that problem and i can already create Monthly visuals :). However I have 2 other issues i want to ask you as well. 

 

I have a dashboard like below where i have 2 years sale data of 2 products Ipad, Laptop for 2 countries UK US. 

There are 2 sets of visual "Selected Period" and "Monthly trend". I have 2 help needed: 

 

1. P6M trend: i would like to see P6M trend ending the last date of selected period. For example for data filter i choose, 1Jan19 to 31Jan19, my P6M visuals will show sales for 6 months Aug18, Sep18, Oct18,Nov18, Dec18, Jan19

 

2. Monthly sales trend: i would like to see all the historical months until last date of selected period. For examle for data filter i choose 1Jan to 31Jan19, the monthly trend visuals will show monthly sales from Jan18 (latest data available) until Jan19 

 

For now it only covers the selected periods only. Thanks so much for your help! 

Capture.PNG

 

 

Hi @v-frfei-msft 

Thanks you very much for the guidance below. I managed to solve that problem and i can already create Monthly visuals :). However I have 2 other issues i want to ask you as well. 

 

I have a dashboard like below where i have 2 years sale data of 2 products Ipad, Laptop for 2 countries UK US. 

There are 2 sets of visual "Selected Period" and "Monthly trend". I have 2 help needed: 

 

1. P6M trend: i would like to see P6M trend ending the last date of selected period. For example for data filter i choose, 1Jan19 to 31Jan19, my P6M visuals will show sales for 6 months Aug18, Sep18, Oct18,Nov18, Dec18, Jan19

 

2. Monthly sales trend: i would like to see all the historical months until last date of selected period. For examle for data filter i choose 1Jan to 31Jan19, the monthly trend visuals will show monthly sales from Jan18 (latest data available) until Jan19 

 

For now it only covers the selected periods only. Thanks so much for your help! 

 

Capture.PNG

 

 
tkirilov
Resolver I
Resolver I

Hi @jinny_le ,

 

I think you can just use Sales in the Value field of the clustered column chart you have there and that should display the sum of sales per month, without the need for a measure. 
In order to display your date in "MMM-YYYY" format, you can change the original Date field to that format in the Date Formatting options under the Modelling tab.

@tkirilov Thanks for your reponse! I tried and it still doesn't work. Even though i change the Date to display as MMM YYYY, i still have multiple Jan 2020 and Feb 2020 data. 

 

sample 4.png

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.