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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
v-sihou-msft
Employee
Employee

@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
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

v-sihou-msft
Employee
Employee

@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,

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.