Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.