Forum Discussion

Subash2345's avatar
Subash2345
Frequent Visitor
11 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 fin...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 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

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
11 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
    Icon for Community Support rankCommunity Support
    10 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
    10 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
      Icon for Community Support rankCommunity Support
      10 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

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        10 months ago

        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