Calculate MTD,LMTD(same period last month),QTD for Fiscal year

[Prajakta11]

Hello my query is i want to calclate MTD ,LMTD and LQTD (for Fiscal year)
I want my calculations as follow
1. MTD_if my current month is june-23 I want data  from 1st june-current day(for instance 8th june)
2.LMTD-this will show data for May-23 I want data from 1st may-8th may
3LQTD(Q4 FY23)-this wil show Average of data from(jan1st-8thjan)+(feb1st-8thfeb)+(march 1st-8th march)

attaching Image for refrence ,Please help 

[lbendlin]

- do you have a calendar table?

- is it in import mode?

- is it refreshed daily?

[Prajakta11]

@lbendlin  
Yes i do have calendar table and it is in import mode wirh daily refreshed 
I am sharing measures which I have tried but nothing seems to work ,Previous month calculation still giving whole month counts 
Please Help 

[lbendlin]

You need to add calculated columns to your calendar table.

IsPastPM = [date]<=EDATE(TODAY(),-1)

etc.

Then use these columns as additional filters.

The usual caveats apply.  The shorter your periods the less useful the comparison.  Keep in mind that consecutive months rarely start on the same weekday (only March in non-leap years).

[Prajakta11]

@lbendlin i got the solution for it ,could you help me out with QTD and LQTD calculations 

[lbendlin]

Same methodology.  Add a calculated column to your calendar table for each of these scenarios.

[Prajakta11]

this is not working in my case 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Prajakta11]

@lbendlin  Sharing sample data with expected results,Please check 

https://docs.google.com/spreadsheets/d/11cD2COFgnBrjV4VvWjTW_UtwrnXyIVgc/edit?usp=sharing&ouid=10564... 

[v-yueyunzh-msft]

Hi , @Prajakta11 

Thanks for your sample data first!

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a calcualted column in calendar date table to get the quarter of the date:

Calendar = ADDCOLUMNS( CALENDAR( FIRSTDATE('Table'[data.date]) , LASTDATE('Table'[data.date])) ,"Quarter" , QUARTER([Date]))

(3)Then we can create a measure like this:

Measure = var _cur_quarter= MAX('Calendar'[Quarter])
var _pre_quarter= _cur_quarter-1
var _date= MAX('Calendar'[Date])
var _t =  FILTER(ALL('Calendar') ,'Calendar'[Quarter] = _pre_quarter && DAY('Calendar'[Date])<= DAY(_date))

return
SUMX(_t,  CALCULATE( SUM('Table'[Counts])))

Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly