Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
snandy2011
Helper IV
Helper IV

DAX : Month To date formula help

 

Hi, Everyone,

 

I have created a moth to date measure by following formula

 

MTD = CALCULATE(SUM('Poker Profit'[Gross Gaming Revenue]), DATESMTD('Calendar'[Date])) 

 

It is showing me the correct numbers. 

 

Now, I am comparing this current month with others month by day wise. Over here I am facing a problem. When i click Other month on th slicer, It shows me those month's profit but, the MTD coulmn is also getting changed into corresponding month profit..I am attaching a screenshort for better understanding.Plase have a look,

 

Screenshot (4).png

 

Look at the scrrenshots, when i have clicked May on the slicer, Total Profit and MTD are both showing me same rasult. but, I want to fix this MTD coulmn.That means, whenever i will click any month on the slicer, the total profit column will be changed accordingly but, MTD column will show me this month value.

 

expected output : 

suppose i have selected may on the slicer

 

Day       MTD          Total profit

Sun       2500           4500

Mon      7500           5000

Tue       3000            4000

 

 

can you please suggest me how can i achieve that?

 

Any suggesation is really appreciable.

 

Thanks,

snandy2011

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @

 

What happens when you use your 'Calendar'[Date] field on a visual with your calculated measure.  It seems strange to use it with a Week Day Name field.  MTD functions will try to cumulate your measure up over the days of the calendar month.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

Hi @

 

What happens when you use your 'Calendar'[Date] field on a visual with your calculated measure.  It seems strange to use it with a Week Day Name field.  MTD functions will try to cumulate your measure up over the days of the calendar month.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

Thanks for your reply.. It is showing wrong result..

 

Screenshot (5).png

 

Please have a look.. It should show only june's value..But it also shows may and other's month value..

Hi @snandy2011

 

That's the way the MTD function works.  For whatever day on whatever month you pass to it, it will return what the cumulative value of the measure is to that day of that month.

 

If you only want to show data for June, then use a slicer to filter on June so that only those days appear on the axis.  This is by design and is actually quite useful.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

hi @Phil_Seamark i have a similar question, but MTD function works giving the cumulative values in the month. I have a mtd function but it does not give me the accumulated value of the current month. the function gives the accumalted value of the last month. The correct amount should be 0 to the current month.

This is my code:

 

Monto_acumulado_cursesl = TOTALMTD(sum(General[Todo_lo_cursado_monto]);General[Fecha_curse_2];ALLSELECTED(General))

 

 

 

 

Hi @Phil_Seamark sir,

 

Thanks for teaching me a wonderful concept. I had completely wrong idea about Month to date. It gives us cumulative total or running total of that particular month whatever month i am passing. I need to clear this concept more.

 

Thanks for this lesson.It was great learning.

 

Thanks,

snandy2011

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.