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

Applying a Common Slicer to two tables without connecting them

Hi, 

 

  • I have two tables (Date and Exec_CivilMonthStats) and a common date column (ReportMonthEnd) in both of them. 

 

  • I want to have ReportMonthEnd column in the Date table to used as a common slicer which I am not able to do or MMMM-YYYY to drive my tables.  

 

  • To give a more context why I am doing this as in Exec_CivilMonthStats table I have a calculated field for "Last twelvemonths data" with below formula

 

Finalized LTM =
VAR CurrentDate = MAX('Date'[ReportMonthEnd])
Var PreviousDate = DATE(YEAR(CurrentDate)-1,MONTH(CurrentDate), DAY(CurrentDate))
Var Result =
CALCULATE(
SUM(Exec_CivilMonthStats[Finalized Flag]),
FILTER(
Exec_CivilMonthStats, Exec_CivilMonthStats[ReportMonthEnd] >=PreviousDate && Exec_CivilMonthStats[ReportMonthEnd]<=CurrentDate
)
)
Return
Result
 
If I use ReportMonthEnd from Exec_CivilMonthStats table, then it narrows down my result to that particular month for "Finalized LTM" instead of showing last twelve months for "Finalized LTM".
 
Please refer to Page 1 and Page 2 of the attached workbook where Page 2 shows the unwanted change to Finalized LTM. https://drive.google.com/open?id=1QmPgG6CJEQzxE9vYtjqR_s1eeVdAMbd-
2 REPLIES 2
Microsoft
Microsoft

Hi @SauravSidana ,

From your description, I could not understand below word:

If I use ReportMonthEnd from Exec_CivilMonthStats table, then it narrows down my result to that particular month for "Finalized LTM" instead of showing last twelve months for "Finalized LTM".

Could you please post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel, 

 

Lets say if select March 2019 in both the pages in the slicer, then Finalized LTM (47209) on page 1 and Finalised Flag(2461) is my desired result. But I am not able to get the both of these on same page of the workbook. 

 

Thanks,  

Saurav Sidana 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors