cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Calculating MTD

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

3 REPLIES 3
Super User

Hi @Anonymous

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

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.

Anonymous
Not applicable

 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%

Community Support

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors