Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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 01MaandnummerCal. year / month.Cal. year / month Level 01.Key.2Cal. year / month.Cal. year / month Level 01.Long Name.1Cal. year / month.Cal. year / month Level 01.Long Name.2Abs Diff M - 3Order Quantity
JAN 202112021January202150100
JAN 202112021January202160400
JAN 202112021January202163148
FEB 202122021February20214901640
FEB 202122021February2021425
MAR 202132021March202110700
MAR 202132021March202140200

 

3 REPLIES 3
VahidDM
Super User
Super User

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/

Anonymous
Not applicable

 

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

 

Cal. year / month.Cal. year / month Level 01MaandnummerCal. year / month.Cal. year / month Level 01.Key.2Cal. year / month.Cal. year / month Level 01.Long Name.1Cal. year / month.Cal. year / month Level 01.Long Name.2Abs Diff M - 3Order Quantity
JAN 202112021January202150100
JAN 202112021January202160400
JAN 202112021January202163148
FEB 202122021February20214901640
FEB 202122021February2021425
MAR 202132021March202110700
MAR 202132021March202140200

 

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%

 

Humberto1959_0-1639495034467.jpeg

 

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

80.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors