custom column to show last month

[Anonymous]

hi all,

using this file, how do i change the custom column so that it show

1) last month

2) Last quarter
3) This quarter

(custom column)

Date.From(Date.AddDays(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10),-7))

any advice is grateful.

[rajulshah]

Hello @Anonymous,

I have queries:
1. Do you want to create calculated columns in DAX or Power Query & why?
2. Do you want the column to show as 'Last Month', 'Last Quarter' or the value of Last Month for the relevant data?

[Anonymous]

@rajulshah 

1) not too sure what is the difference between calculated columns in DAX & Power Query but the end result i need is to to a filter which is able to filter by current month/last month,last quarter, this quarter etc...

I tried using -30 but I know it wrong.

2) column to show Last Month (which mean Nov) and if this month is Jan 2020 (it will show dec as last month)

[rajulshah]

Can you show the expected result you want?

[Anonymous]

@rajulshahend result is something like a filter which is able to filter by

a) current month

b) last month (I tried using -30 but I know it wrong)

c) last quarter

d) this quarter

[v-shex-msft]

Hi @Anonymous ,

I'm not so sure #3(this quarter) means, can you please share explain more about this?
For 'last month' and 'last quarter', you can try to use Date.AddMonths,Date.AddQuarters instead Date.AddDays, they will get an accurate result:

AddColumn1= Table.AddColumn(#"Previous steps", "Last month", each Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-1))

AddColumn2= Table.AddColumn(#"Previous steps", "Last quarter", each Date.AddQuarters(Date.From(DateTime.FixedLocalNow()),-1))

Regards,

Xiaoxin Sheng

[rajulshah]

@Anonymous,

Maybe these link can help you for quarter calculations:
https://community.powerbi.com/t5/Desktop/Current-Quarter-and-last-quarter-calculation/td-p/703345

For month calculations you can do as below:

Previous month Value = CALCULATE([Measure],PREVIOUSMONTH(Date[Calendar Date]))

Current month Value = CALCULATE([Measure],FILTER(Date,MONTH(Date[Calendar Date])=MONTH(TODAY())&& YEAR(Date[Calendar Date])=YEAR(TODAY()))

Hope this helps.