Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using FILTER by respecting other filter applied

Hi all, 

 

I'm using this formula to rank invoicing date within a month with some additional criteria. It works perfectly :

 

Rank number = 
    rankx(
        filter(
            filter(
                filter(
                    filter(
                        PNL,
                        EARLIER(PNL[Plant])=PNL[Plant]
                    ),
                    EARLIER(PNL[Invoice_year])=PNL[Invoice_year]
                ),
                EARLIER(PNL[Invoice_month])=PNL[Invoice_month]
            ),
            EARLIER(PNL[Div)=PNL[Div]
        ),
        PNL[Invoice_date].[Date],,,Dense
    )

 

In my table i have also a colum which tell me for each line if the invoice day is during week-ends or not. But this criteria is not inlcuded in the formula above. 

 

My final intention is to calculate the amount of the last X days of the month and i found how to do it. 

BUT :

I wanted to use this "weekend criteria" in my dashboard as a slicer for taking into account (or not) weekend invoices, and i thought that my Rank_number formula above was taking into account this slicer, but apparently it is not. In this formula, if my Xth last day of invoicing is a sunday, it will stay a sunday and the calculation will not taken into account in the sum, but I want this 7th day to be a friday in order to taken it into account. 

 

Any help on this maybe ? 

 

thank you

 

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , check if following columns in your date tbake can help

     

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
    Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
    Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , check if following columns in your date tbake can help

     

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
    Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
    Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You should use measure instead of calculated column if you want to take the slicer into account. Would you please show us some sample data and expected output by onedrive for business? Then we can help you more correctly.

     

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

     

    Best Regards,

    Dedmon Dai