Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to display KPIs and KPIs result in a matrix for Max month selected in a date slicer. Requirement is to display KPIs result in a matrix for ONLY latest month from selected month range and display all selected months KPI results in a chart on the right.
Issue- Matrix is displaying data for all selected months instead of showing it only for latest month
Matrix screen - >
Measure - >
FIlter applied in Slicer ->
Will greatly appreciate any help on this
Solved! Go to Solution.
Hi , @asharma7803
I download your .pbix file, You want to show only the data for the maximum age of the slicer. Right?
Here are the steps you can refer to :
(1)You can add a column in your 'Date' table:
Date =
ADDCOLUMNS (
CALENDARAUTO(),
"Calendar Year", "CY" & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Year", YEAR( [Date] ),
"Month Year", FORMAT ( [Date], "mmm" ) & "-" &YEAR ( [Date] ),
"year_month", YEAR([Date])*100 + MONTH ( [Date] )
)
(2)We can create a measure like this:
Measure 2 = var _slice = MAXX(ALLSELECTED('Date'),[year_month])
var _current_year = SELECTEDVALUE( 'Date'[year_month])
return
IF(_slice = _current_year ,1,0)
(3)Then we put the measure in the "Filter on this visual" and configure it:
(4)Then we can meet your need , the result is as follows:
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
HI , @asharma7803
Based on your description, you want to show data for the largest month selected by the slicer. Right?
For custom date presentations, we can't associate our Date table with our fact table, you can use the 'Date' table as a slicer, then get the maximum year and month of the selection, and then use the IF function to judge whether the values in the Matri visual are displayed or not.
If this method does not meet your needs, you can provide us with your special sample data(table or .pbix file) and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @v-yueyunzh-msft,
Desired output is based on slicer in first page(Select Review and KPIs) If I select Aug 22 as last month data should appear only for only Aug 22 month as below in KPI Summary page
But data is appearing for last month as well for all previous months where data is available for KPIs.
I have tried if condition but that dint work as well. May be I am missing something.
Attaching PBIX file for your reference.
https://onedrive.live.com/?id=A9A36C9AEC54DDF7%21460&cid=A9A36C9AEC54DDF7
Hi @v-yueyunzh-msft ,
One more thing to add, I have to also show data for all selected months in the chart at right of table( in summary page) so applying Max filter in Filter pane will not also help as it will restrict data to last month in the chart.
Below is the chart in summary page Im talking about.
Sorry , below is the link that will work for PBIX file.
Hi , @asharma7803
You share the link, I can't download your .pbix file after opening it, can you share it with us through OneDrive?
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Can you please try below link
https://onedrive.live.com/?authkey=%21AAK5MRp7hUY7OK4&id=A9A36C9AEC54DDF7%21460&cid=A9A36C9AEC54DDF7
Hi , @asharma7803
I download your .pbix file, You want to show only the data for the maximum age of the slicer. Right?
Here are the steps you can refer to :
(1)You can add a column in your 'Date' table:
Date =
ADDCOLUMNS (
CALENDARAUTO(),
"Calendar Year", "CY" & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Year", YEAR( [Date] ),
"Month Year", FORMAT ( [Date], "mmm" ) & "-" &YEAR ( [Date] ),
"year_month", YEAR([Date])*100 + MONTH ( [Date] )
)
(2)We can create a measure like this:
Measure 2 = var _slice = MAXX(ALLSELECTED('Date'),[year_month])
var _current_year = SELECTEDVALUE( 'Date'[year_month])
return
IF(_slice = _current_year ,1,0)
(3)Then we put the measure in the "Filter on this visual" and configure it:
(4)Then we can meet your need , the result is as follows:
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I have found a solution and it is almost similar to your measure2 logic. But this measure helped me greatly to come up with the solution. Thank you so much:)
Regards,
Anil
Hi @asharma7803
please try
Absolute Result =
CALCULATE (
SUM ( VNKPI_results[ActualValue] ),
'Date'[Date] = MAX ( 'Date'[Date] )
)
Hi @tamrej1
I tried it but getting error as below
Please try
Absolute Result =
VAR MaxDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )
or
Absolute Result =
VAR MaxDate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )
Thank you @tamerj1, I have tried second solution but again returing previous months data. I have already tried your first solution in past and it doesnt wrk as well.
@asharma7803
Ok, Then try
Absolute Result =
VAR MaxDate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ), ALL ( VNKPI_results ) )
RETURN
CALCULATE ( SUM ( VNKPI_results[ActualValue] ), 'Date'[Date] = MaxDate )
Hi @tamrej1,
That dint work as well, data is returned for all months. I need data only for latest month that is August.