MTD Calculation

[Asmaa-elsheikh]

Hi All,

I need your support; I am trying to calculate MTD,but it gives me inacurrate numbers, here the method I used:

=Calculate(SUM(COUNT),MONTH(Updated_Date)=MONTH(TODAY())

 I have used the quick measure in power bi, but it gave me a blanck result

And here is a sample of the data:

Product type	user	Count	Date	Updated_Date
Laptop	A	20	2024-03-17T22:19:11.829Z	2024-03-17
Speaker	C	5	2024-03-17T14:34:36.922Z	2024-03-17
Laptop	C	7	2024-03-17T13:28:05.836Z	2024-03-17
Laptop	E	90	2024-03-17T13:23:58.907Z	2024-03-17
Laptop	A	20	2024-03-18T22:19:11.829Z	2024-03-18
Speaker	C	5	2024-03-18T14:34:36.922Z	2024-03-18
Laptop	C	7	2024-02-17T13:28:05.836Z	2024-02-17
Laptop	E	90	2024-02-17T13:23:58.907Z	2024-02-17

[Anonymous · Accepted solution]

Hi @Asmaa-elsheikh ,

I create a table as you mentioned.

When I write your DAX code, it gives me an error. I think you can change COUNT.

Then I change the DAX code, it gives me the result you want.

Measure =
CALCULATE (
    SUM ( 'Table'[Count] ),
    MONTH ( 'Table'[Updated_Date] ) = MONTH ( TODAY () )
)

Best Regards

Yilong Zhou

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

[Asmaa-elsheikh]

Yes, I need the mTD to show only values for March & to be dynamic to show the MTD per each coming month without the need to filter values manually each month, is there any way to do ?

[audreygerred]

Hi, yes, absolutely. Assuming you have a date dim table and have it marked as the date table, you would create your MTD measure with the quick measure and use the date from the date table in the measure. Then, put your date field from the date table in filters on the page (or entire report of you want) and set it to relative date, in this month. This will allow it to dynamically be in the correct month. 

 

[MNedix]

Hi,

Why not using the built-in functions?

=CALCULATE(SUM(COUNT),DATESMTD(Updated_Date))

[Asmaa-elsheikh]

I have used it, but gives me blank values 

[audreygerred]

Hello! The quick measure works for me. If you are trying to show data for only March, make sure you are filtered to March, otherwise it will be blank since PBI doesn't know which month you want the MTD for. 

Here is a table when I have the month in rows and am using the MTD measure:

Here it is without Month in rows, but I am filtered to March:

Here it is when I am not filtered to any month:

The measure I used: 

Count MTD =

IF(

    ISFILTERED('DateDim'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    TOTALMTD(SUM('FactTable'[Count]), 'DateDim'[Date])

)