Forum Discussion

Subash2345's avatar
Subash2345
Frequent Visitor
8 months ago
Solved

Dynamic Month Filtering

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

Need Help
  • v-dineshya's avatar
    v-dineshya
    8 months ago

    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

7 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    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

    • v-dineshya's avatar
      v-dineshya
      Community Support
      8 months ago

      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

    • Subash2345's avatar
      Subash2345
      Frequent Visitor
      8 months ago

      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's avatar
        v-dineshya
        Community Support
        8 months ago

        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

  • lbendlin's avatar
    lbendlin
    Super User
    8 months ago

    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.