Using FILTER by respecting other filter applied

[Anonymous]

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

[v-deddai1-msft]

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

[amitchandak · Accepted solution]

@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