topic Calculating MTD in DAX Commands and Tips

Calculating MTD

Anonymous — Tue, 14 Dec 2021 11:24:40 GMT

How can I make a DAX formula MTD for only full months. So March has not ended yet so it has to be till Februari with this formula :

DPA M-3_CY = if('KPI''s 2021'[input DPA m-3] = 0, " ", ((1-SUM('KPI''s 2021'[Abs Diff M - 3])/sum('KPI''s 2021'[Order Quantity]))*100))

Cal. year / month.Cal. year / month Level 01	Maandnummer	Cal. year / month.Cal. year / month Level 01.Key.2	Cal. year / month.Cal. year / month Level 01.Long Name.1	Cal. year / month.Cal. year / month Level 01.Long Name.2	Abs Diff M - 3	Order Quantity
JAN 2021	1	2021	January	2021	50	100
JAN 2021	1	2021	January	2021	60	400
JAN 2021	1	2021	January	2021	63	148
FEB 2021	2	2021	February	2021	490	1640
FEB 2021	2	2021	February	2021	4	25
MAR 2021	3	2021	March	2021	10	700
MAR 2021	3	2021	March	2021	40	200

Re: Calculating MTD

VahidDM — Tue, 14 Dec 2021 11:37:56 GMT

Hi Anonymous

Can you post sample data as text and expected output?
Not enough information to go on;

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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Calculating MTD

Anonymous — Tue, 14 Dec 2021 15:20:06 GMT

I read your advice and hopefully this will clarify a bit.

Cal. year / month.Cal. year / month Level 01	Maandnummer	Cal. year / month.Cal. year / month Level 01.Key.2	Cal. year / month.Cal. year / month Level 01.Long Name.1	Cal. year / month.Cal. year / month Level 01.Long Name.2	Abs Diff M - 3	Order Quantity
JAN 2021	1	2021	January	2021	50	100
JAN 2021	1	2021	January	2021	60	400
JAN 2021	1	2021	January	2021	63	148
FEB 2021	2	2021	February	2021	490	1640
FEB 2021	2	2021	February	2021	4	25
MAR 2021	3	2021	March	2021	10	700
MAR 2021	3	2021	March	2021	40	200

When it is for instance half March it should calculate only the sum of Jan/Feb M-3 = 667 divide by sum Jan/Feb order quantity = 2313 = expected output = 29%

Re: Calculating MTD

v-easonf-msft — Tue, 04 Jan 2022 08:38:54 GMT

Hi, Anonymous

If you have a seperated calendar table without any relationship, you can try the formula as below:

Result = VAR _today = SELECTEDVALUE ( 'Calendar table'[Date] ) VAR a = CALCULATE ( SUM ( 'Table'[Abs Diff M - 3] ), FILTER ( ALL ( 'Table' ), 'Table'[Cal. year / month.Cal. year / month Level 01] < STARTOFMONTH ( 'Calendar table'[Date] ) ) ) VAR b = CALCULATE ( SUM ( 'Table'[Order Quantity] ), FILTER ( ALL ( 'Table' ), 'Table'[Cal. year / month.Cal. year / month Level 01] < STARTOFMONTH ( 'Calendar table'[Date] ) ) ) RETURN a / b

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.