Need to getting only rows who have last day of month according to multiple slicer selection

[AmitSaini]

Hi Team,

I need your help to achieve some results in the form of a table chart in Power BI.

We have the dataset as shown in the picture below.

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	1273	08/22/2024	scm - IM	4.7.0
Spark	5092	08/27/2024	scm - IM	4.7.0
Spark	1339	08/28/2024	scm - IM	4.7.0
Spark	1340	09/02/2024	scm - IM	4.7.0
Spark	5360	09/23/2024	scm - IM	4.7.0
Spark	1341	09/24/2024	scm - IM	4.7.0
Spark	1341	09/26/2024	scm - IM	4.7.0
Spark	4023	09/30/2024	scm - IM	4.7.0
Spark	1341	10/01/2024	scm - IM	4.7.0
Spark	4023	10/03/2024	scm - IM	4.7.0
Spark	2779	10/14/2024	scm - IM	4.7.0
Spark	1341	10/29/2024	scm - IM	4.8.0
Spark	1341	10/30/2024	scm - IM	4.8.0
Spark	4025	11/06/2024	scm - IM	4.8.0
Spark	1342	11/07/2024	scm - IM	4.8.0
Spark	2684	11/21/2024	scm - IM	4.8.0
Spark	2684	11/28/2024	scm - IM	4.8.0
Spark	2500	11/29/2024	scm - IM	4.8.0
Spark	2684	11/29/2024	scm - IM	4.8.0
Spark	2684	12/04/2024	scm - IM	4.8.0
Spark	5368	12/05/2024	scm - IM	4.8.0
Spark	2684	12/23/2024	scm - IM	4.8.0
Spark	1342	12/24/2024	scm - IM	4.8.0
Spark	4023	12/30/2024	scm - IM	4.8.0
Spark	4052	12/31/2024	scm - IM	4.8.0
Spark	2740	01/02/2025	scm - IM	4.8.0
Spark	2746	01/08/2025	scm - IM	4.8.0
Spark	1373	01/28/2025	scm - IM	4.8.0
Spark	1373	01/29/2025	scm - IM	4.8.0
Spark	77	09/30/2024	scm-IA	4.7.0
Spark	154	12/05/2024	scm-IA	4.8.0
Spark	80	12/10/2024	scm-IA	4.8.0
Spark	78	12/10/2024	scm-IA	4.8.0
Spark	78	01/02/2025	scm-IA	4.8.0
Spark	84	01/03/2025	scm-IA	4.8.0
Spark	84	01/08/2025	scm-IA	4.8.0

Based on the above dataset, we have three slicers on our page: Project Name, Pipeline Name, and Release Name.

So based on the slicer selection we need to show only those rows who have last day of month only.

For example, if I select Pipeline Name - scm - IM, we need to show only the records for the last day of every month:-

same like below result

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	1339	08/28/2024	scm - IM	4.7.0
Spark	4023	09/30/2024	scm - IM	4.7.0
Spark	1341	10/30/2024	scm - IM	4.8.0
Spark	2500	11/29/2024	scm - IM	4.8.0
Spark	2684	11/29/2024	scm - IM	4.8.0
Spark	4052	12/31/2024	scm - IM	4.8.0
Spark	1373	01/29/2025	scm - IM	4.8.0

same if I am select Pipeline Name - scm-IA then need to show only below result [all records for last day of every month]:-

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	77	09/30/2024	scm-IA	4.7.0
Spark	80	12/10/2024	scm-IA	4.8.0
Spark	78	12/10/2024	scm-IA	4.8.0
Spark	84	01/08/2025	scm-IA	4.8.0

Additionally, we can also filter the data based on Release Name. For example, if I select Pipeline Name - scm - IM and Release Name - 4.7.0, we need to show only the records for the last day of every month.

Need output like below pic.

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	1339	08/28/2024	scm - IM	4.7.0
Spark	4023	09/30/2024	scm - IM	4.7.0
Spark	2779	10/14/2024	scm - IM	4.7.0

and if I am selecting Pipeline Name - scm - IM and Release Name - 4.8.0  then need to get result like below

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	1341	10/30/2024	scm - IM	4.8.0
Spark	2500	11/29/2024	scm - IM	4.8.0
Spark	2684	11/29/2024	scm - IM	4.8.0
Spark	4052	12/31/2024	scm - IM	4.8.0
Spark	1373	01/29/2025	scm - IM	4.8.0

and for Pipeline Name - scm-IA and Release Name - 4.7.0 then need to get result like below

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	77	09/30/2024	scm-IA	4.7.0

and for Pipeline Name - scm-IA and Release Name - 4.8.0 then need to get result like below

Project Name	ResultCount	DateTime	Pipeline Name	ReleaseName
Spark	80	12/10/2024	scm-IA	4.8.0
Spark	78	12/10/2024	scm-IA	4.8.0
Spark	84	01/08/2025	scm-IA	4.8.0

[v-yangliu-msft · Accepted solution]

Thanks for the reply from speedramps  and DataNinja777 , please allow me to add some more information:
Hi  @AmitSaini ,

You can consider creating a measure to dynamically filter the visual once again after it has already been filtered by the slicer

Here are the steps you can follow：

1. Create measure.

Flag =
var _maxdate=
MAXX(
    FILTER(ALLSELECTED('Table'),    YEAR('Table'[DateTime])=YEAR(MAX('Table'[DateTime]))&&MONTH('Table'[DateTime])=MONTH(MAX('Table'[DateTime]))),[DateTime])
RETURN
IF(
    MAX('Table'[DateTime])=_maxdate,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AmitSaini]

Hi @v-yangliu-msft 

Thank you for the solution; it works really well. I appreciate your effort.

[speedramps]

@AmitSaini 

You need to clearly describe the required granularity.

@DataNinja777  has done exactly what you said

and shows the lastest date by:-

• Project name
• Pipeline  name
• Release name
• Month

The 14/10/2024 is for 4.7.0

whereas the 30/10/2024 is for 4.8.0

[speedramps]

Good answer @DataNinja777 

I have copied it to a PBIX which you can download from onedrive

Click here 

It generates different output to the first examples provied by @AmitSaini 

Please can @AmitSaini  check the output carefully and reword the question if necessary to clarify what is required.

Please click thumbs up for me spotting this small discrepancy and providing the PBIX but please accept the excellent solution from @DataNinja777 

[AmitSaini]

Hi @speedramps 

Thanks for highlighting this issue. Yes, you are correct—it should show only the last day entry [30/10/2024] and exclude the entry for (14/10/2024).

I need the same output as explained in my post, but we are okay not to show the release name in the table chart if it creates an issue in building the logic.

Need output table look like: -

Project Name	ResultCount	DateTime	Pipeline Name
Spark	1339	08/28/2024	scm - IM
Spark	4023	09/30/2024	scm - IM
Spark	1341	10/30/2024	scm - IM
Spark	2500	11/29/2024	scm - IM
Spark	2684	11/29/2024	scm - IM
Spark	4052	12/31/2024	scm - IM
Spark	1373	01/29/2025	scm - IM

[DataNinja777]

Hi @AmitSaini ,

To achieve the requirement of displaying only the last day of the month based on slicer selections for Pipeline Name and Release Name, you can create a DAX measure that dynamically filters the dataset. The measure will identify the maximum date within each month for the selected pipeline and release, ensuring only those rows appear in the table visual.

LastDayOfMonthFlag = 
VAR SelectedMonth = MAXX(
    FILTER(ALL('Table'), 
        'Table'[Pipeline Name] = MAX('Table'[Pipeline Name]) &&
        'Table'[ReleaseName] = MAX('Table'[ReleaseName]) &&
        FORMAT('Table'[DateTime], "YYYY-MM") = FORMAT(MAX('Table'[DateTime]), "YYYY-MM")
    ), 
    'Table'[DateTime]
)
RETURN 
IF(MAX('Table'[DateTime]) = SelectedMonth, 1, 0)

After creating this measure, add all relevant columns (Project Name, ResultCount, DateTime, Pipeline Name, and ReleaseName) to a table visual in Power BI. Then, apply a filter on the measure to display only rows where LastDayOfMonthFlag = 1. This ensures that only records corresponding to the last available date within each selected month are shown dynamically based on slicer inputs.

Best regards,

[AmitSaini]

Hi @DataNinja777 

Thank you for your reply. I appreciate your effort and time.

As you have seen, @speedramps  highlighted an issue in the output after creating and implementing the measure you suggested.

As I already mentioned, I need the same output as explained in my post, but we are okay not to show the release name in the table chart if it creates an issue in building the logic.