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 fin...
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

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

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