cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sousahebert Frequent Visitor
Frequent Visitor

Get data from same weekday from last month

Hi,

 

I'd like to know how to get values from same period in last month. 

For exampe:

 

Data                Value

02/01/2018 (2th Month Weekday) -  100

04/12/2018 (2th Month Weekday) -  110

03/01/2018 (3th Month Weekday)  -200

05/12/2018 (3th Month Weekday)  -240

I need the data like this

 

Data                    M0              Previous Month

02/01/2018    100                 110

03/01/2018   200                  240

 

Is it possible?

 

Tks

 

 

10 REPLIES 10
sousahebert Frequent Visitor
Frequent Visitor

Get data from same Workdayfrom last month

Hi,

 


I'd like to know how to get values from same period in last month.

For exampe:

 

Data Value

02/01/2018 (2th Month Workday) - 100

04/12/2018 (2th Month Workday) - 110

03/01/2018 (3th Month Workday) -200

05/12/2018 (3th Month Workday) -240

I need the data like this

 

Data M0 Previous Month

02/01/2018 100 110

03/01/2018 200 240

 

Is it possible?

 

Tks

 

 

ccakjcrx Member
Member

Re: Get data from same Workdayfrom last month

Hey @sousahebert!

 

I may not be understanding you correctly, but here is what I came up with based on how I understood your problem. 

 

Solution.jpg

 

Here is the measure: 

 

MsrValueLastMonth = 
CALCULATE(SUM(Sheet1[Value]),
PREVIOUSMONTH(Sheet1[Date])
)

Hope that helps. If not, leave a comment indicating that is the case.

ccakjcrx Member
Member

Re: Get data from same weekday from last month

@sousahebert

 

You accidentally posted the same thing twice. Can you delete this post? I posted what will hopefully be helpful in the other post; if it is not helpful, just leave a comment in the other post.

sousahebert Frequent Visitor
Frequent Visitor

Re: Get data from same Workdayfrom last month

i'v already tryied it.. but don't worked.....

 

using previousmont or dateadd(date,-1,month) , the result is:

 

Date             M0     M-1

 

04/12/2017  110   

05/12/2017  240    

02/01/2018 100     5000

03/01/2018  200    5000

 

its return the full last month value (5000 its the value between 01/12/2017 and 31/12/2017)....

ccakjcrx Member
Member

Re: Get data from same Workdayfrom last month

Hey @sousahebert!

 

Is there any way you can post your .pbix file? If you've never done that before, you can post your file to either OneDrive or Google Drive, and then post the link to that file here. 

sousahebert Frequent Visitor
Frequent Visitor

Re: Get data from same Workdayfrom last month

Sorry for delay..

You can access the pbix example file in below link

 

https://1drv.ms/u/s!AkSHnifOD9Zuhig6IJ3GE1Yvxkgn

 

Tks!!

v-jianhe-msft Regular Visitor
Regular Visitor

Re: Get data from same Workdayfrom last month

Hi,

 

Forgive me but I am a bit confused about your business logic.

Referring to your second time reply:

 

>>> Date         M0     M-1

 

 

04/12/2017  110  

 

05/12/2017  240   

 

02/01/2018 100     5000

 

03/01/2018  200    5000

 

>>> its return the full last month value (5000 its the value between 01/12/2017 and 31/12/2017)....

 

So,

  1. here, “04/12/2017” means “2017 December 4th”,  “02/01/2018” means “2018 January 1st”, right?
  2. And you want to make “M-1” for “02/01/2018” to be 110, similarly, “M-1” for “03/01/2018” to be 240, right?

 

If so, but what is the relationship between “04/12/2017” and “02/01/2018”?

“2017 December 04” is Monday, while “2018 January 02” is Tuesday, so they are not the same weekday. Obviously, they are not the same workday in two months?

 

Also, but what is the relationship between “05/12/2017” and “03/01/2018”?

“2017 December 05” is Tuesday, while “2018 January 03” is Wednesday, so they are not the same weekday. Obviously, they are not the same workday in two months?

 

So how do you want to match the data between this month’s date and previous month’s date?

 

Best Regards,

Henry

 

ccakjcrx Member
Member

Re: Get data from same weekday from last month

Hey @sousahebert!

 

Sorry I'm just now getting back to this. It looks like you might be after the value from thirty days ago. Here is the formula I used:

 

MsrValueLastMonth = 
SUMX(
    DATEADD(MES_ATUAL[Data de Cadastramento 2],-30,DAY),
    [Qtd_Processo_M0]
)

Assuming that is what you need, this expression should help. It simply goes back thirty days and executes the [Qtd_Processo_M0] measure. You can change the second parameter of the DATEADD expression to go back a certain number of days. You don't have date values for every day of the month in the MES_ATUAL table, so some rows will be blank. Here is what it looks like in the table visual:

 

Screenshot.jpg

 

HERE is a link to your sample file with the added expression.

 

Hope this helps.

 

 

sousahebert Frequent Visitor
Frequent Visitor

Re: Get data from same weekday from last month

Hi

 

I Will Try this.. 

 

Tks!

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,947)