count of date column value not responding to month slicer

[Chandramouli_BI]

I have a date column which consists of date in the format of 26-06-2023 in which 2023 is year, 06 is month & 26 is date. I separated month from date column using the below DAX : 

MonthName = FORMAT('agg_route_trip'[Date], "MMMM").

I have created a slicer range which is count of distinct date. Now the issue is MonthName filter is not working correctly with the slicer. Somebody pls help me with this

Here is the sample data:

https://drive.google.com/file/d/1LUYxFUm_YNbO57f2Xmad2HZSyt1cz6NL/view?usp=sharing 

BELOW IS THE EXACT REQUIREMENT FROM CLIENT

I have created a scatter plot with 16 grids(percentile lines 25th,50th,75th & 100th in PowerBI analytics pane option). In X-axis service wise average EPK & in Y-axis service operated days(Which is count of distinct date column service wise). The requirement is Client wants to check services run less than 10 days(For ex) in a particular month (Ex July). For this requirement I have added month slicer & also avg EPK &  service operated days(count of distinct date). Both service wise Avg EPK & service wise operated days is not working proprly with month filter. Based on month & count of distinct date slicers the scatter plot should filter the data

 Below are the DAX used:

Service_Distinct Date Count =

CALCULATE(

    DISTINCTCOUNT('agg_route_trip'[Date]),

    ALLEXCEPT('agg_route_trip', 'agg_route_trip'[service])

)

Service Average EPK =

CALCULATE(

    AVERAGE('agg_route_trip'[epk]),

    ALLEXCEPT('agg_route_trip', 'agg_route_trip'[Service])

)

[Ashish_Mathur]

Hi,

Not clear about what you want but try this.  Create a Calendar Table with calculated column formlas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Table to the Date column of the Calendar Table.  To yoru visuals/filter/slicer, drag Month name/Date from the Calendar Table.

[Chandramouli_BI]

I have created a scatter plot with 16 grids(percentile lines 25th,5oth,75th & 100th in PowerBI analytics pane option). In X-axis service wise average EPK & in Y-axis service operated days(Which is count of distinct date column). The requirement is Client wants to check services run less than 10 days in a particular month (Ex July). For this requirement I have added month slicer & also avg EPK &  service operated days(count of distinct date). Avg EPK is working fine with month but service operated days is not working proprly with month filter. Please help me with this

[Ashish_Mathur]

Still not clear.  Someone who is will help you.

[Chandramouli_BI]

Hi sir,

In PowerBI I have a datekey column in my table & based on this column I created date column & based on date column I created month column also. Bus run days is count of distinct date column. Now the requirement of client is they wanted to check month wise or year wise bus run days which is count of distinct dates. For example they might want to know in month of july & year 2023 buses run less than 15 days. Based on this I used month slicer & count of distinct date slicer as a range. Based on these 2 slicers I want to see the buses. But after selecting month slicer(Ex: July) I wanted to check buses run less than 15 days in july month so, I selected a range from 1 to 15 in count of distinct dates slicer. But the data is not filtered correctly. Please help me with this

Based on the month & count of distinct dates value slicers I want to see the services in scatter plot visual or Based on these 2 slicers I want to see the services

Please find the below sample file

https://drive.google.com/file/d/1LUYxFUm_YNbO57f2Xmad2HZSyt1cz6NL/view?usp=sharing 

[Kishore_KVN]

Hello @Chandramouli_BI ,

Check in edit interactions once. There is nothing much to impact if your explanation is correct. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[Chandramouli_BI]

Hi @Kishore_KVN , I used edit interactions but still not working

[Fowmy]

@Chandramouli_BI 

Where did you get the field for the slicer, I mean the distinct date?
If you are using multiple tables, check if you have made done the relationship correctly.
Also, check the Interactivity under Format Tab