Dynamic Month Filtering

[Subash2345]

Dynamic Month Filtering Original.pbix

My requirement is in my Power BI, I have a Financial Year (July-Jun) slicer and a Month slicer. 

1. If I select the Finacial Year year before current financial year- it should display all months, but for current Financial year it should diaply only until completed month

2. The visuals should display the value until selected month - YTD and MTD for selected month

I have created a DummyDate disconnected table, and its working on the line chart visual but the filter(months) are not coming as it has to - meaning if FY2025/26, it should display only July-Dec and FYs before that July-June

[Praful_Potphode]

Hi @Subash2345 ,

try below measure:

Current year = TOTALYTD(SUM('Fact Table'[MTD Values]),DateTable[Date],"06/30")

Last  year = TOTALYTD(SUM('Fact Table'[MTD Values]),PARALLELPERIOD(DateTable[Date],-1,YEAR),"06/30")

06/30 signifies that your fiscal year start monthis july and end date is june.

Sample PBIX

please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Subash2345]

Hi @Praful_Potphode 

Dynamic Month Filtering - Also still I am unable to acheive the month till current/previous month in the Dummy Date slicer.

I cant use that formula on the MTD/YTD values as they are calculated and coming out from Tabular model. I am sorry I did not mention this in my initial message. 

[v-dineshya · Accepted solution]

Hi @Subash2345 ,

Please refer below updated DAX code.

IsBeforeCurrentMonth =

VAR TodayDate = TODAY()

VAR CurrMonth = MONTH(TodayDate)

VAR CurrYear  = YEAR(TodayDate)

VAR CurrentFY =

    IF( CurrMonth >= 7,

        CurrYear,        

        CurrYear - 1      

    )

VAR RowFY =

    VALUE( MID([FinancialYear], 3, 4) )  

VAR CurrentFYMonth =

    IF(

        CurrMonth >= 7,

        CurrMonth - 6,    

        CurrMonth + 6        

    ) - 1                    

VAR RowMonthFY = [MonthNumberFY]

RETURN

IF(

    RowFY < CurrentFY,      

    TRUE(),

    IF(

        RowFY = CurrentFY    

            && RowMonthFY <= CurrentFYMonth,

        TRUE(),

        FALSE()

    )

)

ShowMonthInSlicer =

IF (

    SELECTEDVALUE(DummyDate[IsBeforeCurrentMonth], FALSE() ) = TRUE(),

    1,

    0

)

Please refer below output snaps.

1. As you mentioned, If I select the Finacial Year year before current financial year- it should display all months.

2.  As you mentioned , for current Financial year it should display only until completed month.  THis month is december, you mentioned to show till completed month (till november month). Please refer below snap.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Subash2345 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Subash2345 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Praful_Potphode  and @lbendlin , Thank you for your prompt responses.

Hi @Subash2345 , could you please try the proposed solution shared by  @Praful_Potphode ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[lbendlin]

Add a calculated column to your calendar table . Name it "IsBeforeCurrentMonth" and set it to

[date] < DATE(YEAR(TODAY(),MONTH(TODAY(),1)

Then use that column as a filter on all pages.

[Subash2345]

Dynamic Month Filtering Tried.pbix I have tried that solution but it addresses my Month filter appearnce but not filtering the metrics/visuals as intended. Financial Year is so disconnected and its not working