Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
answeriver
Helper IV
Helper IV

Same period last month by working days

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
21 REPLIES 21
answeriver
Helper IV
Helper IV

@v-eachen-msft  ?

 

Anyone can help me in this?

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@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

Dear @v-eachen-msft?,

Did you downloaded the file?

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 () )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@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?

@v-eachen-msft Hello.

 

So can we find the decision ?

@v-eachen-msftHello.

So do you have any tips for of my question?

v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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"

 

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] )
                )
    )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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

@v-eachen-mstf do you have any ideas?
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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

 

 

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)

Sorry froget add user

@amitchandak  please explain

@answeriver , I suggested a column

@amitchandakThanks for your answer.

Can you explain?

@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)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.