Same period last month by working days

[answeriver]

Hi all,

How i can use formula which help me make sameperiod last month with same count of working days in two periods.

Now i use this, but it's error (red text)

SPLM = CALCULATE([mln st];REPORT_IMS[VISIT YEAR]=YEAR(TODAY());'REPORT_IMS'[VISIT MONTH]=MONTH(TODAY())-1;REPORT_IMS[WORKING DAY]<=CALCULATE(MAX(REPORT_IMS[WORKING DAY];DATESMTD(REPORT_IMS[VISIT DATE]))))

How i can fix it?

Basically i need sales (mln st) in previous month in same count of working days in this month

[answeriver]

@v-eachen-msft  ?

Anyone can help me in this?

[v-eachen-msft]

Hi @answeriver ,

Could you please share your sample data and expected result to me if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

[answeriver]

@v-eachen-msft  sorry but i can't share trought OneDrive.

Here is google link

https://drive.google.com/open?id=130o5zto6H26IB9vPb3pVNKO8n_iQGN8r

It's OK?

Actually data have information about clients, but i think is not necessary information

[answeriver]

Dear @v-eachen-msft?,

Did you downloaded the file?

[v-eachen-msft]

Hi @answeriver ,

According to your sample data, I found it always returns "22". It seems that you returned max working days from the whole table. You could use a filter to replace DATESMTD().

FILTER (
    REPORT_IMS,
    REPORT_IMS[VISIT YEAR] = YEAR ( TODAY () )
        && REPORT_IMS[VISIT MONTH] = MONTH ( TODAY () )
)

[answeriver]

@v-eachen-msft 

Because the period which i shared to you has 22 working days. If i take more date, so it will about 19-22 in some month.

But i dont understand measure above,  there is not relationship with working days?

[answeriver]

@v-eachen-msft Hello.

So can we find the decision ?

[answeriver]

@v-eachen-msftHello.

So do you have any tips for of my question?

[v-eachen-msft]

Hi @answeriver ,

I found that your DAX may missed a ")" behind MAX() function. It should be:

CALCULATE(MAX(REPORT_IMS[WORKING DAY] ) ;DATESMTD(REPORT_IMS[VISIT DATE]))

You could check if it works.

[answeriver]

Hello @v-eachen-msft  you right, but the problem is "A function CALCULATE has been used in a True/False expression ... This is not allowed"

[v-eachen-msft]

Hi @answeriver ,

According to this error message, you could add a filter contains the True/False formula. Here is the DAX for your reference.

SPLM =
CALCULATE (
    [mln st];
    FILTER (
        'REPORT_IMS';
        'REPORT_IMS'[VISIT YEAR] = YEAR ( TODAY () )
            && 'REPORT_IMS'[VISIT MONTH]
                = MONTH ( TODAY () ) - 1
            && REPORT_IMS[WORKING DAY]
                <= CALCULATE (
                    MAX ( REPORT_IMS[WORKING DAY] );
                    DATESMTD ( REPORT_IMS[VISIT DATE] )
                )
    )
)

[answeriver]

@v-eachen-msft Thanks, no more errors, but results showing full previuos month

[answeriver]

@v-eachen-mstf do you have any ideas?

[Greg_Deckler]

Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, see these two as perhaps they will point you in the right direction:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

@answeriver 

Create a Total working day in your date table and use that

Assume you have working day =1 for the working day

Total working day = sumx(filter(Date,Date[Month]= earlier(Date[Month])),[working day ])

Now use this in you measure

Measure =

var _max = Maxx(allselected(Date),[Total working day]) // for the selected month

[answeriver]

Hi,

Total working day = sumx(filter(Date,Date[Month]= earlier(Date[Month])),[working day ])

When i enter this fromula i can choose column in this step (red)

[answeriver]

Sorry froget add user

@amitchandak  please explain

[amitchandak]

@answeriver , I suggested a column

[answeriver]

@amitchandakThanks for your answer.

Can you explain?

[amitchandak]

@answeriver , please find a new solution

Have columns like

Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Day This month = if('Date'[Work Day]=1,sumx(filter('Date',[Month Year]=EARLIER('Date'[Month Year]) && [Date]<=EARLIER('Date'[Date]) ),[Work Day]),BLANK())

now you can have measure like

MTD Working Day=
var _max = maxx(allselected('Date'),[Work Day This month])
return
calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),'Date'[Work Day This month]<=_max)

LMTD Working Day=
var _max = maxx(allselected('Date'),[Work Day This month])
return
CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,month)),'Date'[Work Day This month]<=_max)

or
LMTD Working Day=
var _max = maxx(allselected('Date'),[Work Day This month])
return
CALCULATE(Sum('order'[Qty]),previousmonth('Date'[Date]),'Date'[Work Day This month]<=_max)