cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelJackpbi Regular Visitor
Regular Visitor

show both ytd by month& mtd by day in a dashboard by using month slicer

Hi friends,

I have a month slicer and 2 measures including Sales_MTD and Sales_YTD, now i need to show up both in a dashboard to see:

- A Clustered Column chart to show Sales_MTD by day

- A Clustered Column chart to show Sales_YTD by month

 

However, when i select a month( i.e. when i select March) on the slicer i only show the Sales_YTD for March but i need show both Sales_YTD for Jan,Feb,March in a chart. If i select multiple month on slicer, the YTD chart can show correctly but the MTD-chart will incorrect. Please help me to resolve this issue- select a month to show Sales_YTD by month

 

Regards,
J.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: show both ytd by month& mtd by day in a dashboard by using month slicer

@MichaelJackpbi

 

In this scenario, when you select March in Slicer, the current context is March only so that the YTD calculation can't calculate for Jan and Feb. For your requirement, I suggest you create two slicers, one for YTD chart, the other for MTD. You can click the slicer and "Edit Interactions" under Format tab to determine which visual to interact.

 

4.PNG

 

Regards,

 

View solution in original post

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: show both ytd by month& mtd by day in a dashboard by using month slicer

@MichaelJackpbi

 

In this scenario, when you select March in Slicer, the current context is March only so that the YTD calculation can't calculate for Jan and Feb. For your requirement, I suggest you create two slicers, one for YTD chart, the other for MTD. You can click the slicer and "Edit Interactions" under Format tab to determine which visual to interact.

 

4.PNG

 

Regards,

 

View solution in original post

Super User
Super User

Re: show both ytd by month& mtd by day in a dashboard by using month slicer

Hey,

 

not sure if I understand your question correctly, but I give it a try. Assuming that you have a Calendar Table that at least has the columns for your dates, months (1 to 12) and year. And that this table is related to your fact table you can use these calculations to get a MonthToDate value and a YearToDate value.

Please be aware that I'm using two Slicers one for the Month and one for the Year. In my opinion it is necessary to also use a Year slicer to pick just one month of a single year.

 

MonthToDate

MonthToDate = CALCULATE(sum('fact'[SimpleMeasure]);filter(all('Calendar');'Calendar'[Year] = max('Calendar'[Year])&& 'Calendar'[Month] = max('Calendar'[Month])))

YearToDate

YearToDate = CALCULATE(sum('fact'[SimpleMeasure]);filter(all('Calendar');'Calendar'[Year] = max('Calendar'[Year])&& 'Calendar'[Month] <= max('Calendar'[Month])))

And a screen of my report

report - sampleDateCalculations.png

As well as a screen of the relationships

model - sampleDateCalculations.png

 

Hope this helps

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
MichaelJackpbi Regular Visitor
Regular Visitor

Re: show both ytd by month& mtd by day in a dashboard by using month slicer

Thank you so much! but i need to show the chart not list

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)