topic Re: Calculate MTD,LMTD(same period last month),QTD for Fiscal year in DAX Commands and Tips

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

Anonymous — Thu, 08 Jun 2023 05:34:20 GMT

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

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

lbendlin — Sat, 10 Jun 2023 01:58:09 GMT

- do you have a calendar table?

- is it in import mode?

- is it refreshed daily?

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

Anonymous — Mon, 12 Jun 2023 04:43:56 GMT

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

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

lbendlin — Mon, 12 Jun 2023 21:57:24 GMT

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

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

Anonymous — Tue, 13 Jun 2023 05:20:37 GMT

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

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

lbendlin — Tue, 13 Jun 2023 12:40:21 GMT

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

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

Anonymous — Wed, 14 Jun 2023 04:43:16 GMT

this is not working in my case

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

lbendlin — Wed, 14 Jun 2023 10:35:30 GMT

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-Forum/ba-p/963216
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/1447523

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

Anonymous — Fri, 16 Jun 2023 07:22:29 GMT

lbendlin Sharing sample data with expected results,Please check

https://docs.google.com/spreadsheets/d/11cD2COFgnBrjV4VvWjTW_UtwrnXyIVgc/edit?usp=sharing&ouid=105643774708082133910&rtpof=true&sd=true

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

v-yueyunzh-msft — Fri, 16 Jun 2023 08:57:46 GMT

Hi , Anonymous

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